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;