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配置”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！