MySQL复制技术

========================================================

重置数据库：
# service mysqld stop
# rm -rf /usr/local/mysql/data/*
# /usr/local/mysql/scripts/mysql_install_db –user=mysql –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data

 

一、主/备均为刚初始的数据库
第一种方案
单主到多从：  Master-MultiSlave
                    Master                Slave1                    Slave2                
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
IP                    192.168.10.6        192.168.10.37        192.168.10.158   
Server_ID        6                        37                            158                        

1. 主库
[root@master ~]# vim /etc/my.cnf
log-bin=mysql-bin
server-id  = 6
[root@master ~]# service mysqld start
[root@master ~]# mysql
mysql> reset master;
mysql> grant replication slave, replication client on *.*
    -> to rep1@'192.168.10.%' identified by 'tianyun';
mysql> flush privileges;

2. 备库
[root@slave1 ~]# vim /etc/my.cnf
log-bin=mysql-bin
server-id  = 37
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1
[root@slave1 ~]# service mysqld start
[root@slave1 ~]# mysql
mysql> reset master;
mysql> change master to
    -> master_host='192.168.10.6',
    -> master_user='rep1',
    -> master_password='tianyun',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=0;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.10.6
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
            
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.6
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 354
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 500
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3. 测试
Master：
mysql> show processlist\G
*************************** 2. row ***************************
     Id: 2
   User: rep1
   Host: 192.168.10.37:50915
     db: NULL
Command: Binlog Dump
   Time: 324
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
2 rows in set (0.00 sec)

mysql> create database bbs;
Query OK, 1 row affected (0.00 sec)

mysql> create table bbs.t1(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into bbs.t1 values(1);
Query OK, 1 row affected (0.02 sec)
mysql> select * from bbs.t1;
+——+
| id   |
+——+
|    1 |
+——+
1 row in set (0.00 sec)

Slave：
mysql> show processlist\G
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 356
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 3
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: -173772
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
3 rows in set (0.00 sec)

mysql> select * from bbs.t1;
+——+
| id   |
+——+
|    1 |
+——+
1 row in set (0.03 sec)

二、针对已经运行一段时间的主库实现主/备
第一种方案
单主到多从：  Master-MultiSlave
                    Master                Slave1                    Slave2                
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
IP                    192.168.10.6        192.168.10.37        192.168.10.158 
Server_ID        6                        37                            158                    

Master：
1. 主库
[root@master ~]# vim /etc/my.cnf
log-bin=mysql-bin
server-id  = 6
[root@master ~]# service mysqld start
[root@master ~]# mysql
mysql> reset master;
mysql> grant replication slave, replication client on *.*
    -> to rep1@'192.168.10.%' identified by 'tianyun';
mysql> flush privileges;

2. 备库
[root@slave1 ~]# vim /etc/my.cnf
log-bin=mysql-bin
server-id  = 158
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1
[root@slave1 ~]# service mysqld start
[root@slave1 ~]# mysql3
mysql> reset master;

========================================================
初始化备库（使其和主库数据一致）：
Master：
mysql> flush tables with read lock;            //主服务器锁定表
[root@master ~]# mysqldump –single-transaction –all-databases > all.sql
[root@master ~]# mysql -e 'show master status'
+——————+———-+————–+——————+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 |      699 |              |                  |
+——————+———-+————–+——————+
mysql> unlock tables;                                //解锁表
[root@master ~]# rsync -va all.sql 192.168.10.158:/

Slave：
mysql> source /all.sql
========================================================

mysql> change master to
    master_host='192.168.10.6',
     master_user='rep1',
     master_password='tianyun',
     master_log_file='mysql-bin.000001',
     master_log_pos=0;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.10.6
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
            
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.6
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 354
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 500
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

//mysql跳过主从同步错误设置
    mysql>slave stop;
    mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER =1;
    mysql>slave start;

三、常见复制拓朴
1. 一主库多备库
2. 主库，分发主库以及备库
3. 主——主复制（双主）

 

 

 

第二种方案： A －B －C
主          从（主）                从

规划：
            Master                Slave1(Master)        Slave2
IP            192.168.2.51        192.168.2.25            192.168.2.110
Server_ID    51                    25                        110

以第二个实验的主从为基础：
==================================================================================
Slave1(Master) 192.168.2.110 分发库
# vim /etc/my.cnf
。。。
log-bin=mysql-bin                                        //启用binlog
log-slave-updates                                        //该指定的作用是将中继日志中的SQL语句保存到binlog中
# service mysqld restart

授权: 
mysql> grant replication slave on *.*  to slave3@'192.168.2.111' identified by '123';
mysql> flush privileges;

分别锁定 Master、    Slave1(Master)表，防止数据变化
mysql> flush tables with read lock;

分发库
# mysqldump –all-database > /tmp/all.sql            //Slave1(Master)    
mysql> show master status\G

分别解锁Master、    Slave1(Master)
mysql> unlock tables;

# rsync -a /tmp/all.sql 192.168.2.111:/                //将备份的数据库传至Slave2

Slave2
=====================================================================================
# mysql -uroot -p123456 < /all.sql                         //恢复数据库
# mysql -h 192.168.2.110 -uslave3 -p123                //连接测试
# vim /etc/my.cnf
server-id=111
# service mysqld restart

# mysql
92.168.2.111
mysql> slave stop;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    -> master_host='192.168.2.110',
    -> master_user='slave3',
    -> master_password='123',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=98;
Query OK, 0 rows affected (0.00 sec)
mysql> slave start;
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.2.110
                Master_User: slave3
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000003
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000003
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.2.110
                Master_User: slave3
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000003
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000003
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000002
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 1007
                 Last_Error: Error 'Can't create database 'pp'; database exists' on query. Default database: 'pp'. Query: 'create database pp'

 

192.168.2.110
mysql> show master status;
+——————+———-+————–+——————+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000003 |       98 |              |                  | 
+——————+———-+————–+——————+
1 row in set (0.00 sec)

1
测试：
192.168.2.115
mysql> create database ttt;
Query OK, 1 row affected (0.01 sec)

mysql> create table ttt.a1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into ttt.a1 values(333);
Query OK, 1 row affected (0.00 sec)

mysql> select * from ttt.a1;
+——+
| id   |
+——+
|  333 | 
+——+
1 row in set (0.00 sec)

192.168.2.110
mysql>  select * from ttt.a1;
+——+
| id   |
+——+
|  333 | 
+——+

192.168.2.111
mysql>  select * from ttt.a1;
+——+
| id   |
+——+
|  333 | 
+——+
1 row in set (0.02 sec)

排错方法：
slave:
0. vim /etc/my.cnf; service mysqld restart
1. reset slave;
2. change master to      //show master status\G
3. slave stop;
4. slave start;
5. show slave status\G