master master mysql_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>