mysql的master和slave配置

這篇文章主要介紹“mysql的master和slave配置”，在日常操作中，相信很多人在mysql的master和slave配置問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”mysql的master和slave配置”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

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配置”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！