mysql主从复制架构及实现

主从配置

0、master1为主，master2为从节点

1、开启主节点的二进制日志、serverID

[root@master1 ~]# vim /etc/my.cnf[mysqld]log_bin=master-binserver-id=1innodb-file-per-table=ONskip_name_resolve=ONsystemctl start mariadb.serviceMariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%';| log_bin                                   | ON      MariaDB [(none)]> SHOW MASTER LOGS;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |       264 || mysql-bin.000002 |       264 || mysql-bin.000003 |       264 || mysql-bin.000004 |       245 |+------------------+-----------+MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%server%';+----------------------+-------------------+| Variable_name        | Value             |+----------------------+-------------------+| character_set_server | latin1            || collation_server     | latin1_swedish_ci || server_id            | 1                 |+----------------------+-------------------+

2、主节点创建一个有写权限的账户

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'10.201.106.%' IDENTIFIED BY 'replpass';Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec)

3、从节点配置开启中继日志，创建唯一ID号

[mysqld]innodb_file_per_table=ONrelay-log=relay-logrelay-log-index=relay-log.indexserver-id=7skip_name_resolve=ONMariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%';| relay_log                                 | relay-logMariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%server%';+----------------------+-------------------+| Variable_name        | Value             |+----------------------+-------------------+| character_set_server | latin1            || collation_server     | latin1_swedish_ci || server_id            | 7                 |+----------------------+-------------------+

4、从节点连接主服务器，并启用复制线程

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.201.106.131',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=498;MariaDB [(none)]> SHOW SLAVE STATUS\G*************************** 1. row ***************************               Slave_IO_State:                   Master_Host: 10.201.106.131                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: master-bin.000003          Read_Master_Log_Pos: 498               Relay_Log_File: relay-log.000001                Relay_Log_Pos: 4        Relay_Master_Log_File: master-bin.000003             Slave_IO_Running: No            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: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 498              Relay_Log_Space: 245              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: NULLMaster_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: 01 row in set (0.05 sec)启动复制线程：MariaDB [(none)]> START SLAVE;

5、验证同步

MariaDB [(none)]> CREATE DATABASE mydb;MariaDB [(none)]> SHOW MASTER STATUS;+-------------------+----------+--------------+------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-bin.000003 |     1087 |              |                  |+-------------------+----------+--------------+------------------+

从节点：

MariaDB [(none)]> SHOW SLAVE STATUS\G 1. row Slave_IO_State: Waiting for master to send eventMaster_Host: 10.201.106.131Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000003Read_Master_Log_Pos: 1087