MySql主主相互備份的配置過程

假設兩台MySql的IP分別為：

MySql-Master 192.168.0.1
MySql-Slave 192.168.0.2

1.配置MySql-Master

~# vi /etc/mysql/my.cn
server-id               = 238 #唯一id，一般用ip的最後一段
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_ignore_db        = mysql #設定不用同步的表
binlog_ignore_db        = information_schema
binlog_ignore_db        = performance_schema
auto-increment-increment = 2 #因為我們有兩台機子，這裡插入資料的自增長設為2
auto-increment-offset = 1 #這個庫中每插入一條自增長的位移量為1

2.重啟一下mysql

~# service mysql restart

3.設定一個複製master的mysql使用者給slave

~# mysql -uroot -p'welcome'
mysql>  CREATE USER rep1@'192.168.0.2' IDENTIFIED BY 'welcome';
mysql> GRANT REPLICATION SLAVE ON *.* TO rep1@'192.168.0.1';
4.查看一下master的狀態

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |       435 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.配置MySql-Slave

~# vim /etc/mysql/my.cnf
server-id               = 129
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
replicate-ignore-db     = mysql #不用複製的表
replicate-ignore-db     = information_schema
replicate-ignore-db     = performance_schema
log-slave-updates       = ON
relay_log               = mysqld-relay-bin

6.重啟一下mysql

~# service mysql restart

7.設定一下master，並查看一下slave狀態，確認一下是否OK

mysql> change master to
    -> master_host='192.168.0.1',
    -> master_user='rep1',
    -> master_password='welcome',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=329;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

8.建立一個mysql使用者給master來複製slave

mysql>  CREATE USER rep2@'192.168.1.1' IDENTIFIED BY 'welcome';
mysql> GRANT REPLICATION SLAVE ON *.* TO rep2@'rep2@'192.168.1.1';

9.配置salve的自增長和位移

~# vim /etc/mysql/my.cnf

read-only               = 0
binlog-ignore-db        = mysql
binlog-ignore-db        = information_schema
binlog-ignore-db        = performance_schema
auto-increment-increment = 2
auto-increment-offset = 2

~# service mysql restart

10.設定master複製slave，其實就是把slave當真master的主來複製

~# vim /etc/mysql/my.cnf
replicate-ignore-db     = mysql
replicate-ignore-db     = information_schema
replicate-ignore-db     = information_schema       
relay_log               = mysqld-relay-bin
log-slave-updates       = ON

mysql> change master to
    -> master_host='192.168.0.2',
    -> master_user='rep2',
    -> master_password='welcome',
    -> master_log_file='mysql-bin.000008',
    -> master_log_pos=433;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

~# service mysql restart

OK,可以測試一下匯入資料庫，添刪改查一下試試。