0.前期准备
- 两台装有MySql(5.7)的服务器
- 主数据库所在服务器IP:192.168.0.201
- 从数据库所在服务器IP:192.168.0.202
1.修改my.cnf
主数据库(192.168.0.201)
$ vi /etc/my.cnf
在文件末尾添加
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id
从数据库(192.168.0.202)
$ vi /etc/my.cnf
在文件末尾添加
server-id=2 #设置server-id
2.配置主数据库
$ systemctl restart mysqld
$ mysql -hlocalhost -uname -ppassword
mysql > set global validate_password_policy=0;
mysql > select @@validate_password_length;
mysql > set global validate_password_length=1;
mysql > select @@validate_password_length;
mysql > CREATE USER 'slave1'@'192.168.0.202' IDENTIFIED BY 'password';
mysql > GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'192.168.0.202';
mysql > flush privileges;
mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3.配置从数据库
$ systemctl restart mysqld
$ mysql -hlocalhost -uname -ppassword
mysql > CHANGE MASTER TO MASTER_HOST='192.168.0.201',MASTER_USER='slave1',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;
mysql > start slave;
mysql > show slave status\G;
当Slave_IO_Running和Slave_SQL_Running都为YES
4.配置不需要同步的数据库
master开启二进制日志后默认记录所有库所有表的操作,可以通过配置来指定只记录指定的数据库甚至指定的表的操作,具体在mysql配置文件的[mysqld]可添加修改如下选项:
# 不同步哪些数据库
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
# 只同步哪些数据库,除此之外,其他不同步
binlog-do-db = game