#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto_increment_increment = 2
auto_increment_offset = 1
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
5. 创建三种角色的用户
表格 1
| 角色 |
功能 |
权限 |
| monitor user |
MMM（Multi-Master replication manager of MYSQL）监控各主控的健康状况 |
REPLICATION CLIENT |
| agent user |
MMM代理用来设置只读属性，复制主控等 |
SUPER, REPLICATION CLIENT, PROCESS |
| replicate user |
用来复制 |
REPLICATION SLAVE |
表格 2
| (db1)mysql> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'115.29.198.150' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec)
(db1)mysql> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'42.96.%.%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec)
(db1)mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'42.96.%.%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) |
三、 同步DB1和DB2数据库
首先假设DB1包含正确的数据（即使是空数据库），进行DB1和DB2直接的数据同步。
1. 以下在DB1所在服务器上执行数据导出
| (db1)mysql> FLUSH TABLES WITH READ LOCK; (db1)mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 616 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) (db1)mysql> |
2. 另开一个命令窗口导出数据
| (db1)# mysqldump -u root -p --all-databases > /tmp/database-backup.sql |
3. 解锁第一个窗口
| (db1)mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
(db1)mysql> |
4. 将DB1导出的数据导入DB2
1) 拷贝到DB2
| (db1)# scp database-backup.sql root@192.168.0.11:/tmp/ The authenticity of host '192.168.0.11 (192.168.0.11)' can't be established. ECDSA key fingerprint is 55:84:03:9e:d9:74:cc:cd:03:59:23:3f:df:d9:77:a5. Are you sure you want to continue connecting (yes/no) yes Warning: Permanently added '192.168.0.11' (ECDSA) to the list of known hosts. root@192.168.0.11's password: database-backup.sql 100% 528KB 527.9KB/s 00:00 (db1):/tmp# |
2) 导入DB2
| (db2)# mysql -u root -p < /tmp/database-backup.sql Enter password: (db2)# |
3) 应用权限
| (db2)mysql> FLUSH PRIV |