#准备环境
- 主从数据库版本和安装环境一致:MySQL5.7.20
- 主数据库服务器:192.168.1.12
- 从数据库服务器:192.168.1.13
安装MySQL数据库,参考MySQL5.7单机版安装或MySQL单机版安装 - RPM
主数据库设置(192.168.1.12)
###编辑“/etc/my.cnf”配置文件
[root@localhost ~]# vim /etc/my.cnf
配置mysqld节点的“log-bin”、“server-id”、“binlog-ignore-db”:
[mysqld]
log-bin=mysql-bin # [必须]启用二进制日志,
server-id=1 # [必须]服务器唯一ID,默认是1,一般取IP最后一段
binlog-ignore-db = mysql # 忽略MySQL自带数据库,不做主从同步
binlog-ignore-db = information_schema # 忽略MySQL自带数据库,不做主从同步
binlog-ignore-db = performance_schema # 忽略MySQL自带数据库,不做主从同步
binlog-ignore-db = sys # 忽略MySQL自带数据库,不做主从同步
……
注意事项:
mysqld节点的“binlog-do-db”,配置要同步的数据库,如果多个数据库,则配置多行
binlog-do-db=testdb1
binlog-do-db=testdb2
binlog-do-db=testdb……
配置完成后重启数据库:
[root@localhost ~]# /etc/init.d/mysqld restart
###配置数据库同步账户
[root@localhost bin]# ./mysql -uroot -p
Enter password:
mysql> grant all privileges on *.* to '用户名'@'%' identified by '用户密码' with grant option;
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000002 | 7215 | | mysql,information_schema,performance_schema,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
配置从数据库(192.168.1.13)
###编辑“/etc/my.cnf”配置文件
[root@localhost ~]# vim /etc/my.cnf
配置mysqld节点的“server-id”:
[mysqld]
server-id=2 # [必须]服务器唯一ID,默认是1,一般取IP最后一段
……
注意事项:
从库mysqld节点的“log-bin”、“binlog-ignore-db”、“binlog-do-db”可以不配置
配置完成后重启数据库:
[root@localhost ~]# /etc/init.d/mysqld restart
###配置从数据库
[root@localhost bin]# ./mysql -uroot -p
Enter password:
mysql> change master to master_host='192.168.1.12',master_user='用户名',master_password='用户密码',master_log_file='mysql-bin.000002',master_log_pos=7215;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.12
Master_User: zjf
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 7215
Relay_Log_File: localhost-relay-bin.000004
Relay_Log_Pos: 1313
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……
注意事项:
上述“用户名”为主数据库用于数据同步的账户
“start slave”启动主从数据库数据同步
[start|stop] slave 对应[启动|停止] 主从数据库同步
测试主从数据库数据同步
主数据库(192.168.1.12)
登录主数据库,创建数据库、创建表、创建数据库
[root@localhost bin]# ./mysql -uroot -p
Enter password:
mysql> create database testdb default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)
mysql> use testdb;
Database changed
mysql> create table test_user(id varchar(36) not null, username varchar(64) null, dt datetime null, primary key(id));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test_user(id,username,dt) values(uuid(),DATE_FORMAT(NOW(),'test-%Y%m%d%H%i%s'),NOW());
Query OK, 1 row affected (0.00 sec)
……
从数据库(192.168.1.13)
登录主数据库,创建数据库、创建表、创建数据库
[root@localhost bin]# ./mysql -uroot -p
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
mysql> use testdb;
Database changed
mysql> select * from test_user;
+--------------------------------------+---------------------+---------------------+
| id | username | dt |
+--------------------------------------+---------------------+---------------------+
| 8030e500-2a86-11e8-a848-000c29ad7349 | test-20180318162939 | 2018-03-18 16:29:39 |
+--------------------------------------+---------------------+---------------------+
1 row in set (0.00 sec)
完成主从数据库数据同步配置
注意事项:
主数据库的“/etc/my.cnf”–> mysqld -->“log-bin”、“server-id”必须配置
完成配置“/etc/my.cnf”需要重启数据库