mysql master和slave配置

一、master節點
master節點建立賬號並授權
mysql> create user backup identified by '1234';
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> grant all on *.* to backup;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>

master節點配置兩個引數
[root@node2 ~]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
validate_password = OFF

server-id=1
log-bin=mysql-bin

重啟mysqld服務
mysql> show master status
-> ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

二、slave節點配置
[root@node3 ~]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

validate_password = OFF

log_bin = mysql-bin
server_id = 2
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1
~

重啟mysqld服務
[root@node3 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@node3 ~]#
[root@node3 ~]#
[root@node3 ~]#

配置具體的引數
[root@node3 ~]# mysql -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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>
mysql>
mysql>
mysql> change master to master_host='192.168.10.11',
-> master_user='backup',
-> master_password='1234',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.10.11
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
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: 4
Relay_Log_Space: 154
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: NULL
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: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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)

啟動slave服務
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
檢視slave服務啟動是否正常
mysql>
mysql>
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.10.11
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
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: 4
Relay_Log_Space: 154
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1593
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
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: 160310 15:12:18
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)

看到slave服務有一個沒有啟動
Slave_IO_Running: No
Slave_SQL_Running: Yes

檢視mysql日誌報1593錯誤
2016-03-10T07:19:04.236362Z 6 [Warning]
2016-03-10T07:19:04.236675Z 7 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2016-03-10T07:19:04.239318Z 7 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000001' at position 4, relay log './mysql-relay-bin.000001' position: 4
2016-03-10T07:19:04.241677Z 6 [Note] Slave I/O thread for channel '': connected to master 'backup@192.168.10.11:3306',replication started in log 'mysql-bin.000001' at position 4
2016-03-10T07:19:04.249077Z 6 [ERROR] Slave I/O for channel '': , Error_code: 1593
2016-03-10T07:19:04.249110Z 6 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000001', position 4

三、1593錯誤處理

檢視server_id是否重複，看到兩個節點的server_id是不一樣的。
mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 1 |
| server_id_bits | 32 |
+----------------+-------+
2 rows in set (0.00 sec)

mysql> exit
Bye

mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 2 |
| server_id_bits | 32 |
+----------------+-------+
2 rows in set (0.01 sec)

mysql>
mysql>
mysql>
mysql> exit

檢視server-uuid是否重啟，可以看到兩個節點的uuid一樣，主要是因為我的slave虛擬機器是複製master的
[root@node2 mysql]# more auto.cnf
[auto]
server-uuid=cdf9834e-df88-11e5-99cd-080027701d15
[root@node2 mysql]#

drwxr-x--- 2 mysql mysql 4096 Mar 4 15:08 testdb
[root@node3 mysql]# more auto.cnf
[auto]
server-uuid=cdf9834e-df88-11e5-99cd-080027701d15

修改slave節點的uuid
[root@node3 mysql]# vi auto.cnf
[auto]
server-uuid=cdf9834e-df88-11e5-99cd-080027701d16
~
~
重啟master和slave的msyql服務
[root@node2 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@node2 ~]# mysql -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[root@node3 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@node3 ~]# mysql -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and

重啟slave服務Slave_IO_Running、Slave_SQL_Running都正常
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.11
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 367
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: 154
Relay_Log_Space: 740
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_UUID: cdf9834e-df88-11e5-99cd-080027701d15
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)

ERROR:
No query specified

mysql>

mysql日誌也顯示正常啟動
2016-03-10T08:16:34.330158Z 1 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000002' at position 311 for channel ''
2016-03-10T08:16:34.330185Z 1 [Warning]
2016-03-10T08:16:34.330583Z 1 [ERROR] Slave I/O for channel '': error reconnecting to master 'backup@192.168.10.11:3306' - retry-time: 60 retries: 1, Error_code: 2003
2016-03-10T08:17:32.159968Z 2 [Note] Error reading relay log event for channel '': slave SQL thread was killed
2016-03-10T08:17:32.160582Z 1 [Note] Slave I/O thread killed during or after a reconnect done to recover from failed read
2016-03-10T08:17:32.160604Z 1 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000002', position 311
2016-03-10T08:17:48.372597Z 6 [Warning]
2016-03-10T08:17:48.373286Z 7 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2016-03-10T08:17:48.374626Z 7 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000002' at position 311, relay log './mysql-relay-bin.000004' position: 320
2016-03-10T08:17:48.378924Z 6 [Note] Slave I/O thread for channel '': connected to master 'backup@192.168.10.11:3306',replication started in log 'mysql-bin.000002' at position 311

四、驗證
在master節點建立一張表後，slave節點自動同步新建的表
mysql> use testdb;
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>
mysql>
mysql>
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test |
| test1 |
+------------------+
2 rows in set (0.00 sec)

mysql> create table test2 select * from test1;
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test |
| test1 |
| test2 |
+------------------+
3 rows in set (0.00 sec)

mysql>
mysql>
mysql>

mysql> use testdb;
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>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test |
| test1 |
+------------------+
2 rows in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test |
| test1 |
| test2 |
+------------------+
3 rows in set (0.00 sec)

mysql>
mysql>
mysql>

mysql master和slave配置

相關文章