master-master 就是两台服务器互为主从:
master1-slave(master2) && master2-slave(master1)
- master1: 192.168.100.191
- master2: 192.168.100.166
master1-slave(master2)
mysql 版本最好一致
设置 master1
-
创建给 slave 登录用的用户名密码:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.100.166' IDENTIFIED BY 'yNZE7fK9*@aMA?*ppF'; # humanreadable mysql> # grant replication slave on *.* to 'backup'@'192.168.100.166' identified by 'yNZE7fK9*@aMA?*ppF'; mysql> create database hello default charset utf8;
-
配置 my.cnf
# vim /etc/my.cnf [mysqld] server-id=1 #主机id,整数 #开启二进制日志,并且名称为 /var/lib/mysql/mysql-bin.*** # 如果是个路径则,保存到该路径下(log-bin=/var/log/mysql-bin.log -> /var/log/mysql-bin.***) log-bin=mysql-bin read-only=0 #主机读写权限,读写都可以 binlog-do-db=hello #记录日志的数据库:需要的备份数据,多个写多行 # binlog-do-db=hello2 binlog-ignore-db=mysql #不记录日志的数据库:不需要备份的数据库,多个写多行 binlog-ignore-db=test binlog-ignore-db=information_schema # 自增字段奇数递增,防止冲突(1, 3, 5, ...,) auto-increment-increment = 2 # 每次递增的步长 auto-increment-offset = 1 # 初始值
重启 mysql:
serivce mysqld restart
-
导出数据库
锁定数据库,不要退出 mysql shell
mysql>FLUSH TABLES WITH READ LOCK; mysql># flush tables with read lock;
新开一个终端,导出数据库
mysqldump --master-data -uroot -p hello > hello.sql
查看主服务器的状态
mysql> show master status\G; *************************** 1. row *************************** File: mysql-bin.000001 Position: 98 Binlog_Do_DB: hello Binlog_Ignore_DB: mysql,test,information_schema 1 row in set (0.00 sec) ERROR: No query specified
记下 Position 和 File 的值。 解锁
mysql> UNLOCK TABLES; mysql> # unlock tables;
设置 slave(master2)
-
编辑 my.cnf
# vim /etc/my.cnf [mysqld] server-id=2 log-bin=mysql-bin replicate-do-db =hello #只复制某个库,多个写多行 replicate-ignore-db=mysql #不复制某个库 replicate-ignore-db=test replicate-ignore-db=information_schema relay-log=mysqld-relay-bin # 开启日志中继 log-slave-updates # slave将复制事件写进自己的二进制日志 #5.5 #log-slave-updates = ON #5.1 #log-slave-updates = 1
-
导入 master 导出的数据库:
mysql> create database hello default charset utf8; mysql -uroot -p hello < hello.sql
-
配置 master 信息:
mysql> slave stop; mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.100.191', //主服务器的IP地址 -> MASTER_USER='backup', //同步数据库的用户 -> MASTER_PASSWORD='yNZE7fK9*@aMA?*ppF', //同步数据库的密码 -> MASTER_CONNECT_RETRY=60, // 如果从服务器发现主服务器断掉,重新连接的时间差(秒) -> MASTER_LOG_FILE='mysql-bin.000001', //主服务器二进制日志的文件名(前面要求记住的 File 参数) -> MASTER_LOG_POS=98; //日志文件的开始位置(前面要求记住的 Position 参数) mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.191', MASTER_USER='backup', MASTER_PASSWORD='yNZE7fK9*@aMA?*ppF', MASTER_CONNECT_RETRY=60, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98; mysql > slave start;
重启 master, slave:
service mysqld restart
查看 slave 状态:
进入 slave mysql:
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.100.191 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 98 Relay_Log_File: mysqld-relay-bin.000001 Relay_Log_Pos: 98 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: hello Replicate_Ignore_DB: mysql,test,information_schema Exec_Master_Log_Pos: 98 Relay_Log_Space: 98 Until_Log_Pos: 0 Seconds_Behind_Master: NULL 1 row in set (0.00 sec)
注意一定要有下面两项,没有的话查看错误日志(less /var/log/mysqld.log):
Slave_IO_Running: Yes Slave_SQL_Running: Yes
-
测试
master 服务器
mysql> use hello; Database changed mysql> create table test(id int); mysql> insert int test set id=1; mysql> show master status\G; *************************** 1. row *************************** File: mysql-bin.000002 # 注意这里 Position: 276 # 注意这里 Binlog_Do_DB: hello Binlog_Ignore_DB: mysql,test,information_schema 1 row in set (0.00 sec)
slave 服务器
mysql> use hello; mysql> show tables; +-----------------+ | Tables_in_hello | +-----------------+ | test | +-----------------+ 1 row in set (0.00 sec) mysql> select * from test; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.191 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 # 跟 master 一样 Read_Master_Log_Pos: 276 # 跟 master 一样 Relay_Log_File: mysqld-relay-bin.000003 Relay_Log_Pos: 413 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: hello Replicate_Ignore_DB: mysql,test,information_schema Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 276 Relay_Log_Space: 413 Seconds_Behind_Master: 0 1 row in set (0.00 sec)
master2-slave(master1)
master2:
mysql> grant replication slave on *.* to 'backup'@'192.168.100.191' identified by 'yNZE7fK9*@aMA?*ppF'; mysql> flush tables with read lock; mysql> show master status\G; *************************** 1. row *************************** File: mysql-bin.000002 Position: 276 Binlog_Do_DB: Binlog_Ignore_DB: mysql> unlock tables;
vim /etc/my.cnf:
# as master #开启二进制日志,并且名称为 /var/log/mysql/mysql-bin.*** log-bin=mysql-bin read-only=0 #主机读写权限,读写都可以 binlog-do-db=hello #记录日志的数据库:需要的备份数据,多个写多行 binlog-ignore-db=mysql #不记录日志的数据库:不需要备份的数据库,多个写多行 binlog-ignore-db=test binlog-ignore-db=information_schema # 自增字段偶数递增,防止冲突(2, 4, 6, ...,) auto-increment-increment = 2 # 每次递增的步长 auto-increment-offset = 2 # 初始值
service mysqld restart
slave:
mysql> slave stop; mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.166', MASTER_USER='backup', MASTER_PASSWORD='yNZE7fK9*@aMA?*ppF', MASTER_CONNECT_RETRY=60, MASTER_LOG_FILE='mysql-bin.000002, MASTER_LOG_POS=276'; mysql> slave start;
vim /etc/my.cnf
# as slave replicate-do-db =hello #只复制某个库,多个写多行 replicate-ignore-db=mysql #不复制某个库 replicate-ignore-db=test replicate-ignore-db=information_schema relay-log=mysqld-relay-bin # 开启日志中继 log-slave-updates # slave将复制事件写进自己的二进制日志
serivce mysqld restart
测试
master2:
mysql> use hello; mysql> insert into test set id=2; mysql> select * from test; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) mysql> show master status\G; *************************** 1. row *************************** File: mysql-bin.000003 Position: 187 Binlog_Do_DB: hello Binlog_Ignore_DB: mysql,test,information_schema 1 row in set (0.00 sec)
slave:
mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.166 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 187 Relay_Log_File: mysqld-relay-bin.000003 Relay_Log_Pos: 324 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: hello Replicate_Ignore_DB: mysql,test,information_schema Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 187 Relay_Log_Space: 324 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Seconds_Behind_Master: 0 mysql> use hello; mysql> select * from test; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)
最后的配置文件
master1:
# /etc/my.cnf [mysqld] # as master server-id=1 #主机id,整数 #开启二进制日志,并且名称为 /var/lib/mysql/mysql-bin.*** log-bin=mysql-bin read-only=0 #主机读写权限,读写都可以 binlog-do-db=hello #记录日志的数据库:需要的备份数据,多个写多行 # binlog-do-db=hello2 binlog-ignore-db=mysql #不记录日志的数据库:不需要备份的数据库,多个写多行 binlog-ignore-db=test binlog-ignore-db=information_schema # as slave replicate-do-db =hello #只复制某个库,多个写多行 replicate-ignore-db=mysql #不复制某个库 replicate-ignore-db=test replicate-ignore-db=information_schema relay-log=mysqld-relay-bin # 开启日志中继 log-slave-updates # slave将复制事件写进自己的二进制日志 # 自增字段奇数递增,防止冲突(1, 3, 5, ...,) auto-increment-increment = 2 # 每次递增的步长 auto-increment-offset = 1 # 初始值
master2:
# /etc/my.cnf [mysqld] # as slave server-id=2 log-bin=mysql-bin replicate-do-db =hello #只复制某个库,多个写多行 replicate-ignore-db=mysql #不复制某个库 replicate-ignore-db=test replicate-ignore-db=information_schema relay-log=mysqld-relay-bin # 开启日志中继 log-slave-updates # 示slave将复制事件写进自己的二进制日志 # as master #开启二进制日志,并且名称为 /var/lib/mysql/mysql-bin.*** log-bin=mysql-bin read-only=0 #主机读写权限,读写都可以 binlog-do-db=hello #记录日志的数据库:需要的备份数据,多个写多行 # binlog-do-db=hello2 binlog-ignore-db=mysql #不记录日志的数据库:不需要备份的数据库,多个写多行 binlog-ignore-db=test binlog-ignore-db=information_schema # 自增字段偶数递增,防止冲突(2, 4, 6, ...,) auto-increment-increment = 2 # 每次递增的步长 auto-increment-offset = 2 # 初始值