mysql5.7主从同步配置 centos7（简洁步骤）

1.主机配置

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
 
transaction-isolation = READ-COMMITTED
key_buffer_size = 32M
max_allowed_packet = 32M
thread_stack = 256k
thread_cache_size = 64
query_cache_limit = 8M
query_cache_size = 64M
query_cache_type = 1
max_connections = 550
 
server-id = 1
log_bin = /var/lib/mysql/mysql_master.log
binlog_format = mixed
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
 
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_buffer_pool_size = 4G
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_log_file_size = 512M
 
character_set_server = utf8
 
[client]
 
[mysqld_safe]
log-error=/var/log/mysql/mysql.log
pid-file=/var/run/mysql/mysql.pid
sql_mode=STRICT_ALL_TABLES
 
 
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

注意 server-id = 1 log_bin = /var/lib/mysql/mysql_slave.log

2.从机配置

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
 
transaction-isolation = READ-COMMITTED
key_buffer_size = 32M
max_allowed_packet = 32M
thread_stack = 256k
thread_cache_size = 64
query_cache_limit = 8M
query_cache_size = 64M
query_cache_type = 1
max_connections = 550
 
server-id = 2
log_bin = /var/lib/mysql/mysql_slave.log
binlog_format = mixed
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
 
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_buffer_pool_size = 4G
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_log_file_size = 512M
 
character_set_server = utf8
 
[client]
 
[mysqld_safe]
log-error=/var/log/mysql/mysql.log
pid-file=/var/run/mysql/mysql.pid
sql_mode=STRICT_ALL_TABLES
 
 
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

注意 server-id = 2 log_bin = /var/lib/mysql/mysql_slave.log

3.主从设置（主机）

• 登陆主机 mysql

mysql -uroot -p

• 创建同步专用账号

grant replication slave on *.* to 'repl'@'%' identified by '123456';

• 查看状态 查看主机master状态

show master status;

4.主从设置（从机）

• 登陆从节点mysql(root角色）

mysql -uroot -p

• 执行主从设置

change master to  master_host='192.168.6.6' ,master_user='repl',  master_password='123456',master_port=3306,master_log_file='mysql_master.000001',master_log_pos=578;

注意： master_log_file、master_log_pos就是master的file、position

• 起动从机

start slave;

• 查看同步状态

show slave status \G;

常见问题

常见问题：Slave_SQL_Running = NO 这个需要重新从0开始同步主库与从库，操作如下：

master

#登陆mysql
mysql -uroot -p

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
EXIT;

mysqldump -u root -p --all-databases > mysqldump.sql

#再次登陆mysql,执行解锁
mysql -uroot -p

UNLOCK TABLES;

slave

#导入数据
mysql -uroot -p < mysqldump.sql
# 进入mysql 执行
mysql -uroot -p

RESET SLAVE;
CHANGE MASTER TO master_host='192.168.88.203',\
                        master_user='repl',\
                        master_password='123456',\
                        MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
START SLAVE;
SHOW SLAVE STATUS;