mysql5.7的安装与配置

Mysql二进制安装与配置优化

首先将我们下载好的二进制包上传到服务器上：

添加mysql用户：

useradd -s /sbin/nologin -M -u800 mysql

解压并移动到程序目录：

tar -zxvf  mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/

可以重命名一下：

cd /usr/local/

mv mysql-5.7.28-linux-glibc2.12-x86_64  mysql-5.7.28创建mysql软链接：

ln -s /usr/local/mysql-5.7.28/ /usr/local/mysql

赋予安装目录权限:

chown -R mysql.mysql /usr/local/mysql

初始化数据库: 这里如果根分区空间不足 可以适当调整数据库存储路径

创建mysql日志目录：

mkdir -p /data/mysqldata/{data,binlog,logs}

touch  /data/mysqldata/logs/mysql.err

touch /data/mysqldata/binlog/mysql-bin.index

赋权日志目录：

chown -R  mysql:mysql  /data/mysqldata

初始化数据库：初始化数据库会有一个初始密码

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysqldata/data

复制启动脚本：

cp  /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld

chmod  700  /etc/init.d/mysqld

备份以及编辑配置文件：

mv /etc/my.cnf{,.bak}

vim /etc/my.cnf

默认配置：

[client]

port=3306

socket=/tmp/mysql.sock

 

[mysqld]

port=3306

user=mysql

socket=/tmp/mysql.sock

pid-file=/data/mysqldata/data/mysql.pid

basedir=/usr/local/mysql

datadir=/data/mysqldata/data

 

# 一般查询日志，审计使用

#general_log=on

#general_log_file=/data/mysqldata/logs/general.log

log-error=/data/mysqldata/logs/mysql.err

 

# 二进制日志

server-id=39

log-bin=/data/mysqldata/binlog/mysql-bin

binlog_format=ROW

 

# 慢日志

slow_query_log=on

slow_query_log_file=/data/mysqldata/logs/mysql-slow.log

long_query_time=2000

log_queries_not_using_indexes

lower_case_table_names=1

expire_logs_days = 7

 

 

character-set-server = utf8mb4

skip_name_resolve = 1

back_log = 1024

max_connections = 1000

max_connect_errors = 1000000

table_open_cache = 1024

table_definition_cache = 1024

table_open_cache_instances = 64

thread_stack = 512K

max_allowed_packet = 100M

sort_buffer_size = 4M

join_buffer_size = 4M

thread_cache_size = 768

tmp_table_size = 32M

max_heap_table_size = 32M

tmp_table_size = 32M

max_heap_table_size = 32M

log-error =/data/mysqldata/logs/mysql.err

min_examined_row_limit = 100

server-id = 144205

 

 

innodb_buffer_pool_size = 10G

innodb_flush_log_at_trx_commit = 0

innodb_io_capacity = 4000

innodb_write_io_threads = 8

innodb_read_io_threads = 8

innodb_file_per_table = 1

 

 

 

 

设置开机自启动:

chkconfig --add mysqld

chkconfig mysqld on

 

设置全局变量：

echo 'PATH=/usr/local/mysql/bin/:$PATH' >>/etc/profile

source /etc/profile

启动服务:

/etc/init.d/mysqld start

 

mysql 5.7 root 修改密码

update user set authentication_string=password('XXXX') where user='root';

alter user 'root'@'localhost' identified by 'mysqltest1231';

 

create user 'alex'@'%' identified by '123';

#创建一个密码为123的alex用户，%表示所有人都可在任何机器上登陆

 

mysql 给用户赋权 对库增删改查

GRANT ALL PRIVILEGES ON *.* TO 'alex'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;