mysql5.7主从配置

安装mysql

基本环境

master CentOS7 192.168.56.201
slave CentOS7 192.168.56.202

安装mysql

# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
# yum -y install mysql57-community-release-el7-10.noarch.rpm
# yum -y install mysql-community-server

去/var/log/mysqld.log中查看临时密码。

master配置

编辑 my.cnf 配置文件

vi /etc/my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

登录mysql

# mysql -uroot -p

创建同步帐号及授权

mysql> CREATE USER 'repl'@'192.168.56.%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.56.%';
mysql> flush privileges;

给root用户全部权限

mysql> GRANT ALL ON *.* TO 'root'@'locahost';
mysql> flush privileges;

让root用户可以远程登录

mysql> use mysql
mysql> update user set host='%' where user='root';
mysql> flush privileges;

查看主库状态

mysql> show master status;

注：这里的file 和 pos 会配置到slave中。

slave配置

编辑 my.cnf 配置文件

vi /etc/my.cnf

[mysqld]
server-id=2
replicate-do-db = test
replicate-ignore-db = mysql

登录mysql

# mysql -uroot -p

给root用户全部权限

mysql> GRANT ALL ON *.* TO 'root'@'locahost';
mysql> flush privileges;

让root用户可以远程登录

mysql> use mysql
mysql> update user set host='%' where user='root';
mysql> flush privileges;

将主库信息配置到从库中

mysql> stop slave;
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.56.201',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154;
mysql> start slave;

查看从库状态

mysql> show slave status \G;

注：如果 slave_io_running:yes slave_sql_running:yes 代表成功

出现问题查看 /var/log/mysqld.log日志文件