MariaDB设置主从复制

主从复制包含两个步骤: 在 master 主服务器(组)上的设置,以及在 slave 从属服务器(组)上的设置.

配置主服务器 master

1、如果没有启用，则需要开启二进制日志.

给 master 设置唯一的  server_id ,所有的 slave 从属服务器也要设置  server_id; server_id值可以是整数型的数字(1 ~ 2^31-1), 在同一个复制组(replicating group)中的每台服务器的server_id都必须是唯一的.

[mysqld]

server-id=1

log-bin=mysql-bin

binlog_format=mixed

2、创建一个复制账号，并授予replication slave权限。slave 从属服务器需要有连接并从master复制的权限. 通常是为每一台slave 创建一个单独的用户(user),并且只授予复制的权限(REPLICATION SLAVE 权限).

示例

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'slave_host' IDENTIFIED BY 'bigs3cret';   

FLUSH PRIVILEGES;  

    MariaDB [(none)]> grant replication slave on *.* to 'repl_user'@'192.168.1.53' identified by 'pancou';

Query OK, 0 rows affected (0.00 sec)

        MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

需要注意，有一些系统配置选项可能会影响主从复制，查看下面的变量以避免发生问题:

skip-networking,如果 "skip-networking=1"，则服务器将限制只能由localhost连接，阻止其他机器远程连到此服务器上。

bind_address,类似地，如果 服务器只监听 127.0.0.1(localhost)的TCP/IP连接,则远程的 slave也不能连接到此服务器.

3、在主库上，设置读锁定有效，这个操作是为了确保没有数据库操作，以便获得一个一致性的快照：

MariaDB [(none)]> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

    4、然后得到主库上当前二进制文件名和偏移量值。这个操作的目的是在数据库启动以后，从这个点开始进行数据恢复。

        MariaDB [(none)]> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 |      509 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

    5、现在主数据已经停止了更新操作，需要进行主数据库备份。 如果主数据库可以停止那么直接复制数据文件应该是最快的方法。

       [root@localhost ~]# mysqldump -uroot -p --quick --all-databases --lock-all-tables  --master-data=2 > /opt/data-all.sql

  Enter password: 

  [root@localhost ~]# ll -h /opt/data-all.sql 

       -rw-r--r-- 1 root root 3.7M May  2 15:08 /opt/data-all.sql

    6、主库备份完毕以后，可以恢复写操作，剩下的操作只需要在从库上操作。

        MariaDB [(none)]> unlock tables;

        Query OK, 0 rows affected (0.00 sec)

    7、将主数据库的一致性备份数据传送到从库上。

        [root@localhost ~]# rsync -avH --progress '-e ssh -p 22' /opt/data-all.sql root@192.168.1.53:/tmp/

The authenticity of host '192.168.1.53 (192.168.1.53)' can't be established.

RSA key fingerprint is 75:b3:14:47:e1:73:10:24:a8:8f:b8:05:29:3e:7d:30.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.1.53' (RSA) to the list of known hosts.

reverse mapping checking getaddrinfo for bogon [192.168.1.53] failed - POSSIBLE BREAK-IN ATTEMPT!

root@192.168.1.53's password: 

sending incremental file list

data-all.sql

    3863888 100%   23.88MB/s    0:00:00 (xfer#1, to-check=0/1)

sent 3864436 bytes  received 31 bytes  594533.38 bytes/sec

total size is 3863888  speedup is 1.00

配置从属服务器 slave

1、给 slave 指定唯一的 server_id. 所有服务器，不管是主服务器，还是从服务器,都要设置 server_id. server_id值可以是整数型的数字(1 ~ 2^31-1), 在同一个复制组(replicating group)中的每台(/个)服务器的server_id都必须是唯一的.

要让此配置项生效,需要重新启动服务.

  [mysqld]

server-id=2

2、在从库上恢复数据

        [root@localhost ~]# mysql </tmp/data-all.sql 

    3、在从库上使用--skip-salve-start选项启动从服务器，这样不会立即启动从数据库服务器上的复制进程，方便我们对数据库的服务进

       行进一步的设置。

        [root@www ~]# /usr/local/mysql/bin/mysqld_safe --skip-slave-start & 

    4、对从数据库做相应的设置，指定复制使用的用户，指定复制使用的用户，主数据库服务器的IP、端口以及开始执行复制的日志文件和

       位置等。

        CHANGE MASTER TO  

  MASTER_HOST='master.domain.com',  

  MASTER_USER='replication_user',  

  MASTER_PASSWORD='bigs3cret',  

  MASTER_PORT=3306,  

  MASTER_LOG_FILE='mariadb-bin.000096',  

  MASTER_LOG_POS=568,  

  MASTER_CONNECT_RETRY=10;  

        MariaDB [(none)]> change master to master_host='192.168.1.78',master_user='repl_user',master_password='pancou',master_log_file='mysql-bin.000002',master_log_pos=509;

Query OK, 0 rows affected (0.79 sec)

  5、在从库上，启动slave进程

  MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.00 sec)

6、这时在salve上，执行show processlist命令将显示类似如下进程：

  MariaDB [(none)]> show processlist\G

*************************** 1. row ***************************

     Id: 5

   User: root

   Host: localhost

     db: NULL

Command: Query

   Time: 0

  State: init

   Info: show processlist

Progress: 0.000

*************************** 2. row ***************************

     Id: 8

   User: system user

   Host: 

     db: NULL

Command: Connect

   Time: 10

  State: Waiting for master to send event

   Info: NULL

Progress: 0.000

*************************** 3. row ***************************

     Id: 9

   User: system user

   Host: 

     db: NULL

Command: Connect

   Time: 4

  State: Slave has read all relay log; waiting for the slave I/O thread to update it

   Info: NULL

Progress: 0.000

3 rows in set (0.00 sec)

        这表明slave已经连接上master了，并开始接收日志。

        主库上的进程：

        MariaDB [(none)]> show processlist\G

*************************** 1. row ***************************

     Id: 7

   User: root

   Host: localhost

     db: NULL

Command: Query

   Time: 0

  State: init

   Info: show processlist

Progress: 0.000

*************************** 2. row ***************************

     Id: 9

   User: repl_user

   Host: 192.168.1.53:57532

     db: NULL

Command: Binlog Dump

   Time: 183

  State: Master has sent all binlog to slave; waiting for binlog to be updated

   Info: NULL

Progress: 0.000

2 rows in set (0.00 sec)

    7、查看从库复制状态

        MariaDB [(none)]> show slave status\G

*************************** 1. row ***************************

              Slave_IO_State: Waiting for master to send event

                 Master_Host: 192.168.1.78

                 Master_User: repl_user

                 Master_Port: 3306

               Connect_Retry: 60

             Master_Log_File: mysql-bin.000003

         Read_Master_Log_Pos: 479

              Relay_Log_File: localhost-relay-bin.000004

               Relay_Log_Pos: 767

       Relay_Master_Log_File: mysql-bin.000003

            Slave_IO_Running: Yes

           Slave_SQL_Running: Yes

             Replicate_Do_DB: 

         Replicate_Ignore_DB: 

          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: 479

             Relay_Log_Space: 1112

             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: 1

              Master_SSL_Crl: 

          Master_SSL_Crlpath: 

                  Using_Gtid: No

                 Gtid_IO_Pos: 

     Replicate_Do_Domain_Ids: 

 Replicate_Ignore_Domain_Ids: 

               Parallel_Mode: conservative

1 row in set (0.00 sec)

验证复制服务的正确性，在主数据库上执行一个更新操作，观察是否在从库上的是否同步。

MariaDB [(none)]> create database pancou;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use database pancou;

ERROR 1049 (42000): Unknown database 'database'

MariaDB [(none)]> use pancou;

Database changed

MariaDB [pancou]> create table rpel_table(id int(3));

Query OK, 0 rows affected (0.39 sec)

MariaDB [pancou]> insert rpel_table value(1),(2),(3),(4),(5);

Query OK, 5 rows affected (0.04 sec)

Records: 5  Duplicates: 0  Warnings: 0

MariaDB [pancou]> select * from rpel_table;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|    5 |

+------+

5 rows in set (0.00 sec)

    MariaDB [pancou]> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 |      913 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

    查看从库：

    MariaDB [(none)]> select * from pancou.rpel_table;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|    5 |

+------+

5 rows in set (0.00 sec)

MariaDB [(none)]> show slave status\G

*************************** 1. row ***************************

              Slave_IO_State: Waiting for master to send event

                 Master_Host: 192.168.1.78

                 Master_User: repl_user

                 Master_Port: 3306

               Connect_Retry: 60

             Master_Log_File: mysql-bin.000003

         Read_Master_Log_Pos: 913

              Relay_Log_File: localhost-relay-bin.000004

               Relay_Log_Pos: 1201

       Relay_Master_Log_File: mysql-bin.000003

            Slave_IO_Running: Yes

           Slave_SQL_Running: Yes

             Replicate_Do_DB: 

         Replicate_Ignore_DB: 

          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: 913

             Relay_Log_Space: 1546

             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: 1

              Master_SSL_Crl: 

          Master_SSL_Crlpath: 

                  Using_Gtid: No

                 Gtid_IO_Pos: 

     Replicate_Do_Domain_Ids: 

 Replicate_Ignore_Domain_Ids: 

               Parallel_Mode: conservative

1 row in set (0.00 sec)