1, 说明:
首先要准备两台服务器, 一台主服务器 (Master), 另一台从服务器(Slave), 然后要保证 Master 与 Slave 的版本要相同且 Master 不能高于 Slave 的版本, 一般稳健的做法都是使其版本相同, 因为 MySQL 不同版本之间的 binlog(二进制日志) 格式可能会不一样, 最后会导致同步出现异常.
IP | 主机名 | 角色 |
---|---|---|
192.168.1.101 | MySQL-001 | master |
192.168.1.102 | MySQL-002 | slave |
版本:
系统: CentOS 6. 或 7.
MySQL 版本: 5.7
2,master 配置文件设置如下
一般 mysql 配置文件在 / etc/my.cnf
- (如果找不到的话也有可能在这些目录下:/etc/mysql/my.cnf,/usr/local/mysql/etc/my.cnf,~/.my.cnf)
- [root@MySQL-001 ~]# vim /etc/my.cnf
- [mysqld]
- basedir=/usr/local/mysql
- datadir=/data/mysqldata
- socket=/tmp/mysql.sock
- user=mysql
- port=3306
- # master 的配置
- server-id=1 # 服务器 id (主从必须不一样)
- binlog-do-db=employees # 要给从机同步的库
- binlog-ignore-db=mysql # 不给从机同步的库(多个写多行)
- binlog-ignore-db=information_schema
- binlog-ignore-db=performance_schema
- binlog-ignore-db=sys
- log-bin=mysql-bin # 打开日志(主机需要打开), 这个 mysql-bin 也可以自定义;
- expire_logs_days=90 # 自动清理 90 天前的 log 文件, 可根据需要修改
重启数据库使配置生效:
- CentOS 6.*:
- [root@MySQL-001 ~]# service mysqld restart
- [root@MySQL-001 ~]# service mysqld status
- SUCCESS! MySQL running (15607)
- CentOS 7.*:
- [root@MySQL-001 ~]# systemctl restart mysqld.service
- [root@MySQL-001 ~]# systemctl status mysqld.service
- mysqld.service - MySQL Server
- Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)
Active: active (running) since 四 2018-05-17 11:42:02 CST; 2h 5min ago
- Docs: man:mysqld(8)
- http://dev.mysql.com/doc/refman/en/using-systemd.html
- Main PID: 29959 (mysqld)
- CGroup: /system.slice/mysqld.service
- 29959 /opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf
5 月 17 11:42:02 tcloud-118 systemd[1]: Started MySQL Server.
测试 log_bin 是否成功开启
- [root@MySQL-001 ~]# mysql -uroot -p
- mysql> show variables like '%log_bin%';
- +---------------------------------+---------------------------------+
- | Variable_name | Value |
- +---------------------------------+---------------------------------+
- | log_bin | ON |
- | log_bin_basename | /opt/mysql/logs/mysql-bin |
- | log_bin_index | /opt/mysql/logs/mysql-bin.index |
- | log_bin_trust_function_creators | ON |
- | log_bin_use_v1_row_events | OFF |
- | sql_log_bin | ON |
- +---------------------------------+---------------------------------+
- 6 rows in set (0.00 sec)
- mysql>
可以看到 log_bin 为 ON;
3,master 的数据库中建立主从同步账号 backup:
backup 为用户名, 192.168.1.% 表示只允许 192.168.1 网段的客户端连接, 123456 为密码;
- mysql> grant replication slave on *.* to 'backup'@'192.168.1.%' identified by '123456'; # 创建同步账户
- mysql> flush privileges; # 刷新权限
- mysql> select Host,User,authentication_string from mysql.user; # 检查是否创建
- +--------------+---------------+-------------------------------------------+
- | Host | User | authentication_string |
- +--------------+---------------+-------------------------------------------+
- | localhost | root | *6C362347EBEAA7DF44F6D34884615A35095E80EB |
- | localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
- | localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
- | 192.168.1.% | backup | *9BB58B7F11A03B83C396FF506F3DF45727E79614 |
- +--------------+---------------+-------------------------------------------+
- 5 rows in set (0.00 sec)
- mysql>
backup@192.168.1.% 账户已建立;
4,Master 锁表, 往 slave 拷贝数据
重启 MySQL 服务并设置读取锁定, 读取锁定的意思是只能读取, 不能更新, 以便获得一个一致性的快照;
- mysql> flush table with read lock; # 主库锁表; 默认 28800 秒, 即 8 小时自动解锁;
- mysql> show master status \G
- *************************** 1. row ***************************
- File: mysql-bin.000002
- Position: 1621
- Binlog_Do_DB:
- Binlog_Ignore_DB: mysql
- Executed_Gtid_Set: 1d3d078c-59a7-11e8-9a08-00163e000b3f:1-7
- 1 row in set (0.00 sec)
- mysql>
查看主服务器上当前的二进制日志名和偏移量值这里的 file 和 position 要和上面的一致;
导出 master(192.168.1.101)上的数据, 然后导入 slave 中
master:
格式: mysqldump -uUSER -pPASSWORD DATABASE TABLE> NAME.sql
- [root@MySQL-001 ~]# mysqldump -uroot -p employees> /opt/employees.sql # 假如 employees 为主库已经存在的库
- [root@MySQL-001 ~]# yum install openssh-clients -y # 可选
- [root@MySQL-001 ~]# scp /opt/employees.sql root@192.168.1.102:/opt/
- slave:
- [root@MySQL-002 ~]# yum install openssh-clients -y # 可选
5, 配置 slave(192.168.1.102)
- [root@MySQL-002 ~]# vim /etc/my.cnf
- [mysqld]
- basedir=/usr/local/mysql # mysql 程序路径
- datadir=/data/mysqldata # mysql 数据目录
- socket=/tmp/mysql.sock
- user=mysql
- port=3306
- # slave 配置
- server-id=2 # MySQLid 后面 2 个从服务器需设置不同
- skip_slave_start=1 # 复制进程不会随着数据库的启动而启动, 重启数据库后需手动启动;
- # 加上以下参数可以避免更新不及时, SLAVE 重启后导致的主从复制出错.
- read_only = 1 # 从库普通账户只读;
- master_info_repository=TABLE
- relay_log_info_repository=TABLE
- #relay_log_recovery=1 # 从机禁止写
- #super_read_only=1 # 从机禁止写
重启数据库
- CentOS 6.*:
- [root@MySQL-002 ~]# service mysqld restart
- [root@MySQL-002 ~]# service mysqld status
- SUCCESS! MySQL running (15604)
- CentOS 7.*:
- [root@MySQL-002 ~]# systemctl restart mysqld.service
- [root@MySQL-002 ~]# systemctl status mysqld.service
- mysqld.service - MySQL Server
- Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)
Active: active (running) since 四 2018-05-17 11:42:02 CST; 2h 5min ago
- Docs: man:mysqld(8)
- http://dev.mysql.com/doc/refman/en/using-systemd.html
- Main PID: 29959 (mysqld)
- CGroup: /system.slice/mysqld.service
- 29959 /opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf
5 月 17 11:42:02 tcloud-118 systemd[1]: Started MySQL Server.
然后导入到 mysql 数据库中, slave 上的 employees 数据库不存在则先创建, 然后再导入
- [root@MySQL-002 ~]# mysql -uroot -p
- mysql> create database employees; # 新建这个库
- [root@MySQL-002 ~]# mysql -uroot -p employees </opt/employees.sql
登录 slave 数据库, 并做如下设置
- [root@MySQL-002 ~]# mysql -uroot -p
- mysql> stop slave; # 关闭 slave 同步, 第一次可略过;
- mysql> change master to
- -> master_host='192.168.1.101', # master 的 ip
- -> master_user='backup', # 备份用户名
- -> master_password='123456', # 密码
- -> master_log_file='mysql-bin.000002', # 上面截图, 且要与 master 的参数一致
- -> master_log_pos=1621; # 上面截图, 且要与 master 的参数一致
合写为:
- mysql> change master to master_host='192.168.1.101', master_user='backup', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=1621;
- mysql> start slave; # 启动同步
- mysql> show slave status \G # 查看 slave 从机的状态
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.1.101
- Master_User: backup
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000002
- Read_Master_Log_Pos: 1621
- Relay_Log_File: relay-bin.000002
- Relay_Log_Pos: 320
- Relay_Master_Log_File: mysql-bin.000002
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB:
- Replicate_Ignore_DB: mysql
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 1621
- Relay_Log_Space: 521
- Until_Condition: None
- Until_Log_File:
- Until_Log_Pos: 0
- Master_SSL_Allowed: No
- Master_SSL_CA_File:
- Master_SSL_CA_Path:
- Master_SSL_Cert:
- Master_SSL_Cipher:
- Master_SSL_Key:
- Seconds_Behind_Master: 0
- Master_SSL_Verify_Server_Cert: No
- Last_IO_Errno: 0
- Last_IO_Error:
- Last_SQL_Errno: 0
- Last_SQL_Error:
- Replicate_Ignore_Server_Ids:
- Master_Server_Id: 101
- Master_UUID: 1d3d078c-59a7-11e8-9a08-00163e000b3f
- Master_Info_File: mysql.slave_master_info
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
- Master_Retry_Count: 86400
- Master_Bind:
- Last_IO_Error_Timestamp:
- Last_SQL_Error_Timestamp:
- Master_SSL_Crl:
- Master_SSL_Crlpath:
- Retrieved_Gtid_Set:
- Executed_Gtid_Set: 5037e479-59a7-11e8-a35b-00163e000402:1-3
- Auto_Position: 0
- Replicate_Rewrite_DB:
- Channel_Name:
- Master_TLS_Version:
- 1 row in set (0.00 sec)
- mysql>
下面对应参数相同代表设置成功, 0 延时;
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Seconds_Behind_Master: 0
6, 关闭掉主数据库的读取锁定, 并测试
mysql> unlock tables;
在 marster 中创建一个新表再查看 slave 中是否有数据
- master:
- mysql> use employees;
- mysql> create table test001(id int auto_increment primary key,name varchar(20) not null);
- mysql> insert into test001 values(null,'will');
- mysql> insert into test001 values(null,'jim');
- mysql> insert into test001 values(null,'tom');
- mysql> select * from employees.test001;
- +----+------+
- | id | name |
- +----+------+
- | 1 | will |
- | 2 | jim |
- | 3 | tom |
- +----+------+
- 3 rows in set (0.01 sec)
- slave:
- mysql> use employees;
- mysql> select * from employees.test001;
- +----+------+
- | id | name |
- +----+------+
- | 1 | will |
- | 2 | jim |
- | 3 | tom |
- +----+------+
测试 2: 重启关闭从数据库, 主删除 test001 表, 然后主从数据库都重启看是否正常
- master:
- mysql> drop table employees.test001;
- slave:
- mysql> use employees;
- mysql> show tables;
以上实验证明主从同步成功!!!
Linux 下 MySQL 5.7 主从复制(主从同步)
来源: http://www.bubuko.com/infodetail-2661167.html