MySQL主從複製雜記（1）

標籤：MysQL 主從複製 基礎 雜記

mysql主從複製架構及實現主從配置0、master1為主，master2為從節點1、開啟主節點的二進位日誌、serverID

[[email protected] ~]# 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 event
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: 1087

MySQL主從複製雜記（1）