【MySQL】Mariadb主从复制

时间：2021-01-16 14:51:23

Mariadb主从复制

环境配置:

Master : 172.30.200.200

Slave : 172.30.200.204

架构图,如下:

Master的配置:

1.binlog配置

[root@oradb u01]# su - mysql

[mysql@oradb ~]$ mkdir -p /u01/data/binlog

[mysql@oradb ~]$ vi /etc/my.cnf

#*********** binlog related settings ***********

log-bin = /u01/data/binlog/mysql-bin

binlog_format= row

binlog_cache_size=32m

max_binlog_cache_size=64m

max_binlog_size=512m

server-id=30200

2.查看binlog的配置[可选看]

[root@oradb u01]# service mysqld restart

[root@oradb u01]# mysql -uroot -predhat

## 查看binlog开启

MariaDB [(none)]> show variables like '%log_bin%';

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

| Variable_name                   | Value                            |

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

| log_bin                         | ON                               |

| log_bin_basename                | /u01/data/binlog/mysql-bin       |

| log_bin_compress                | OFF                              |

| log_bin_compress_min_len        | 256                              |

| log_bin_index                   | /u01/data/binlog/mysql-bin.index |

| log_bin_trust_function_creators | OFF                              |

| sql_log_bin                     | ON                               |

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

7 rows in set (0.002 sec)

## 查看binlog的格式，是基于行的复制。

MariaDB [(none)]> show variables like '%binlog_format%';

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

| Variable_name              | Value |

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

| binlog_format              | ROW   |

| wsrep_forced_binlog_format | NONE  |

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

## 查看最大的binlog size,到了512m切换一个。

MariaDB [(none)]> show variables like '%max_binlog_size%';

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

| Variable_name   | Value     |

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

| max_binlog_size | 536870912 |

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

1 row in set (0.001 sec)

3.创建replication用户

CREATE USER 'repl'@'172.30.200.204' IDENTIFIED BY 'repl';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.30.200.204';

4.定位二进制位置

## 锁定所有的表，保证数据一致性和完整性

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SHOW MASTER STATUS;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |      667 |              |                  |

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

1 row in set (0.000 sec)

5.使用mysqldump获取数据库快照

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;

[root@oradb ~]# mysqldump -uroot -predhat --all-databases --master-data >dbdump.log

MariaDB [(none)]> UNLOCK TABLES;

SLAVE的配置:

1.配置server-id

[root@oradb ~]# vi /etc/my.cnf

## 在[mysqld]作用域下面配置

server-id=30204

2.导入数据

如果是一个新库，可以不用导入数据。直接配置主从。

如果master已有数据，可以导入之前的dump文件。命令如下;

shell> mysql -uroot -predhat < dbdump.log

3.开启主从复制

 CHANGE MASTER TO MASTER_HOST='172.30.200.200',MASTER_PORT = 3306,MASTER_USER = 'repl',MASTER_PASSWORD = 'repl',MASTER_LOG_FILE = 'mysql-bin.000001',MASTER_LOG_POS = 667;

4.开启复制

MariaDB [(none)]> start slave;

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

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

                Slave_IO_State: Waiting for master to send event

                   Master_Host: 172.30.200.200

                   Master_User: repl

                   Master_Port: 3306

                 Connect_Retry: 60

               Master_Log_File: mysql-bin.000002

           Read_Master_Log_Pos: 342

                Relay_Log_File: oradb-relay-bin.000003

                 Relay_Log_Pos: 641

         Relay_Master_Log_File: mysql-bin.000002

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

               Relay_Log_Space: 1249

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

                Master_SSL_Crl:

            Master_SSL_Crlpath:

                    Using_Gtid: No

                   Gtid_IO_Pos:

       Replicate_Do_Domain_Ids:

   Replicate_Ignore_Domain_Ids:

                 Parallel_Mode: conservative

                     SQL_Delay: 0

           SQL_Remaining_Delay: NULL

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

              Slave_DDL_Groups: 0

Slave_Non_Transactional_Groups: 0

    Slave_Transactional_Groups: 0

1 row in set (0.000 sec)

ERROR: No query specified

数据测试

1.master端插入数据

MariaDB [(none)]> create database zsd;

MariaDB [(none)]> use zsd;

MariaDB [zsd]> create table test(id int,name varchar(20));

MariaDB [zsd]> insert into test values(1,"张盛东");

MariaDB [zsd]> commit;

2.slave端查询数据

MariaDB [zsd]> select * from test;

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

| id   | name      |

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

|    1 | 张盛东    |

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

1 row in set (0.000 sec)

相关文章