前言 Mysql 5.7.20測試主從複製 環境 主庫 192.168.1.59 t-xi-sonar01 從庫 192.168.1.51 t-xi-orc01 設定主機host文件 主庫 [root@t-xi-sonar01 ~]# cat /etc/hosts 127.0.0.1 localho ...
前言 Mysql 5.7.20測試主從複製 環境 主庫 192.168.1.59 t-xi-sonar01 從庫 192.168.1.51 t-xi-orc01 設定主機host文件
主庫 [root@t-xi-sonar01 ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.1.59 t-xi-sonar01 192.168.1.51 t-xi-orc01
從庫 [root@t-xi-orc01 ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.1.51 t-xi-orc01 192.168.1.59 t-xi-sonar01Mysql資料庫配置 【Master-Server】
[root@t-xi-sonar01 ~]# service mysqld stop Stopping mysqld: [ OK ] [root@t-xi-sonar01 ~]# vim /etc/my.cnf #Server ID，一般設置成IP地址的最後一位，如下測試就按後兩位 server_id=59 #開啟log bin，名字最好有意義用來區分 log-bin=dev-bin #需要進行複製的資料庫，可以指定資料庫 #binlog-do-db=DB_master #不需要備份的資料庫，可以設置多個資料庫，一般不會同步mysql這個庫 binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema #為每個session 分配的記憶體，在事務過程中用來存儲二進位日誌的緩存 binlog_cache_size=1m #二進位日誌自動刪除/過期的天數。預設值為0，表示不自動刪除。 expire_logs_days=7 # 跳過主從複製中遇到的所有錯誤或指定類型的錯誤，避免slave端複製中斷。 # 如：1062錯誤是指一些主鍵重覆，1032錯誤是因為主從資料庫數據不一致 slave_skip_errors=1062 [root@t-xi-sonar01 ~]# service mysqld start Starting mysqld: [ OK ] [root@t-xi-sonar01 ~]# mysql 5.7.20登陸報錯解決ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysqld_safe --user=mysql --skip-grant-tables --skip-networking & mysql -u root update user set authentication_string=PASSWORD("****") where User='root'; flush privileges; 主庫創建同步賬戶 service mysqld start mysql> mysql -u root -p mysql> CREATE USER 'replication'@'192.168.1.51' IDENTIFIED BY 'slave'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.51'; mysql> flush privileges; 主庫鎖定後備份將資料同步到從庫 mysql>use sonar mysql>FLUSH TABLES WITH READ LOCK; mysqldump -u root -p --databases sonar > sonar.sql scp sonar.sql @192.168.1.51:/root mysql> unlock tables; [master-server] mysql> show master status ; +----------------+----------+--------------+---------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------+----------+--------------+---------------------------------------------+-------------------+ | dev-bin.000004 | 783 | | mysql,information_schema,performance_schema | | +----------------+----------+--------------+---------------------------------------------+-------------------+
【Slave-Server】
service mysqld stop vim /etc/my.cnf #add slave-server server_id=51 #binlog-ignore-db=mydql #binlog-ignore-db=information_schema #binlog-ignore-db=performance_schema #log-bin=dev-slave-bin binlog_cache_size=1M binlog_format=mixed expire_logs_days=7 slave_skip_errors=1062 relay_log=dev-relay-bin #log_slave_updates=1 read_only=1 service mysqld start 將主庫備份導入從庫 mysql>source /root/sonar.sql 添加鏈接到主庫同步複製的賬戶 mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.59', MASTER_USER='replication', MASTER_PASSWORD='slave', MASTER_LOG_FILE='dev-bin.000001', MASTER_LOG_POS=0; MASTER_LOG_FILE：指定log bin日誌文件名稱 MASTER_LOG_POS ：指定同步複製log分區號，可以從0開始。 查看slave狀態 show slave status \G Slave_IO_State #從站的當前狀態 Slave_IO_Running： Yes #讀取主程式二進位日誌的I/O線程是否正在運行 Slave_SQL_Running： Yes #執行讀取主伺服器中二進位日誌事件的SQL線程是否正在運行。與I/O線程一樣 Seconds_Behind_Master #是否為0，0就是已經同步了 啟動slave start slave;
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.59 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: dev-bin.000004 Read_Master_Log_Pos: 1255 Relay_Log_File: dev-relay-bin.000005 Relay_Log_Pos: 1464 Relay_Master_Log_File: dev-bin.000004 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: 1255 Relay_Log_Space: 1878 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: 59 Master_UUID: d6901902-ea28-11e7-b859-000c29255261 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
【Master-Server】
創建table和database測試 在sonar下建立測試表 mysql> use sonar; mysql> create table slave_t( -> id int(10) not null, name varchar(20) -> ) -> ; Query OK, 0 rows affected (1.57 sec) mysql> insert into slave_t values(1,'name01'); Query OK, 1 row affected (0.33 sec) 創建slave_db測試資料庫 mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> create database slave_db; Query OK, 1 row affected (0.17 sec)
【Slave-Server】
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | slave_db | | sonar | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> use sonar; Database changed mysql> select * from slave_t; +----+--------+ | id | name | +----+--------+ | 1 | name01 | +----+--------+ 1 row in set (0.00 sec)
主庫上的table和database已同步複製過來
【命令參考】
查看主庫master狀態 mysql> show master status; +----------------+----------+--------------+---------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------+----------+--------------+---------------------------------------------+-------------------+ | dev-bin.000004 | 1426 | | mysql,information_schema,performance_schema | | +----------------+----------+--------------+---------------------------------------------+-------------------+ 1 row in set (0.00 sec) 查看從庫主機列表 mysql> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 51 | | 3306 | 59 | 86fff1d0-f62d-11e7-834d-000c29477dac | +-----------+------+------+-----------+--------------------------------------+ 1 row in set (0.00 sec) 查看bin log文件列表 mysql> show binary logs; +----------------+-----------+ | Log_name | File_size | +----------------+-----------+ | dev-bin.000001 | 177 | | dev-bin.000002 | 177 | | dev-bin.000003 | 177 | | dev-bin.000004 | 1426 | +----------------+-----------+ 4 rows in set (0.00 sec) 查看bin log文件的內容 mysql> show binlog events; +----------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +----------------+-----+----------------+-----------+-------------+---------------------------------------+ | dev-bin.000001 | 4 | Format_desc | 59 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 | | dev-bin.000001 | 123 | Previous_gtids | 59 | 154 | | | dev-bin.000001 | 154 | Stop | 59 | 177 | | +----------------+-----+----------------+-----------+-------------+---------------------------------------+ 3 rows in set (0.00 sec) mysql> show binlog events in 'dev-bin.000004'; +----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+ | dev-bin.000004 | 4 | Format_desc | 59 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 | | dev-bin.000004 | 123 | Previous_gtids | 59 | 154 | | | dev-bin.000004 | 154 | Anonymous_Gtid | 59 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | dev-bin.000004 | 219 | Query | 59 | 417 | CREATE USER 'replication'@'192.168.1.51' IDENTIFIED WITH 'mysql_native_password' AS '*51125B3597BEE0FC43E0BCBFEE002EF8641B44CF' | | dev-bin.000004 | 417 | Anonymous_Gtid | 59 | 482 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | dev-bin.000004 | 482 | Query | 59 | 631 | GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.51' | | dev-bin.000004 | 631 | Anonymous_Gtid | 59 | 696 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | dev-bin.000004 | 696 | Query | 59 | 783 | flush privileges | | dev-bin.000004 | 783 | Anonymous_Gtid | 59 | 848 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | dev-bin.000004 | 848 | Query | 59 | 985 | use `sonar`; create table slave_t( id int(10) not null, name varchar(20) ) | | dev-bin.000004 | 985 | Anonymous_Gtid | 59 | 1050 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | dev-bin.000004 | 1050 | Query | 59 | 1123 | BEGIN | | dev-bin.000004 | 1123 | Table_map | 59 | 1177 | table_id: 326 (sonar.slave_t) | | dev-bin.000004 | 1177 | Write_rows | 59 | 1224 | table_id: 326 flags: STMT_END_F | | dev-bin.000004 | 1224 | Xid | 59 | 1255 | COMMIT /* xid=178006 */ | | dev-bin.000004 | 1255 | Anonymous_Gtid | 59 | 1320 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | dev-bin.000004 | 1320 | Query | 59 | 1426 | create database slave_db | +----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+ 17 rows in set (0.13 sec) 查看當前資料庫線程列表 【master-server】 mysql> SHOW PROCESSLIST \G *************************** 1. row *************************** Id: 199 User: root Host: localhost db: mysql Command: Query Time: 0 State: starting Info: SHOW PROCESSLIST *************************** 2. row *************************** Id: 201 User: replication Host: t-xi-orc01:41452 db: NULL Command: Binlog Dump Time: 1450 State: Master has sent all binlog to slave; waiting for more updates Info: NULL *************************** 3. row *************************** Id: 203 User: sonar Host: localhost:57162 db: sonar Command: Sleep Time: 434 State: Info: NULL *************************** 4. row *************************** Id: 204 User: sonar Host: localhost:57358 db: sonar Command: Sleep Time: 123 State: Info: NULL *************************** 5. row *************************** Id: 205 User: sonar Host: localhost:57524 db: sonar Command: Sleep Time: 2 State: Info: NULL *************************** 6. row *************************** Id: 206 User: sonar Host: localhost:57720 db: sonar Command: Sleep Time: 3 State: Info: NULL 6 rows in set (0.00 sec) 【slave-server】 mysql> SHOW PROCESSLIST \G *************************** 1. row *************************** Id: 4 User: root Host: localhost db: sonar Command: Query Time: 0 State: starting Info: SHOW PROCESSLIST *************************** 2. row *************************** Id: 5 User: system user Host: db: NULL Command: Connect Time: 1445 State: Waiting for master to send event Info: NULL *************************** 3. row *************************** Id: 6 User: system user Host: db: NULL Command: Connect Time: 26717 State: Slave has read all relay log; waiting for more updates Info: NULL 3 rows in set (0.00 sec) 從庫啟動複製 mysql> START SLAVE; 從庫停止複製 mysql> STOP SLAVE;
參考： https://segmentfault.com/a/1190000010867488 https://www.jianshu.com/p/208667156faf
