Shulou(Shulou.com)05/31 Report--

This article mainly introduces "master and slave configuration of mysql". In daily operation, I believe many people have doubts about the configuration of master and slave of mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "master and slave configuration of mysql"! Next, please follow the editor to study!

Mysql master and slave configuration

I. master node

Master node creates an account and authorizes it

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 >

The master node configures two parameters

[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

Restart the mysqld service

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)

II. Slave node configuration

[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

~

Restart the mysqld service

[root@node3 ~] # service mysqld restart

Stopping mysqld: [OK]

Starting mysqld: [OK]

[root@node3 ~] #

[root@node3 ~] #

[root@node3 ~] #

Configure specific parameters

[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)

Start the slave service

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Check whether the slave service starts properly

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)

See that one of the slave services is not started

Slave_IO_Running: No

Slave_SQL_Running: Yes

Check the mysql log to report 1593 error

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 mysql-bin.000001' at position O thread for channel': connected to master 'backup@192.168.10.11:3306',replication started in log' 4

2016-03-10T07:19:04.249077Z 6 [ERROR] Slave I for channel':, Error_code: 1593

2016-03-10T07:19:04.249110Z 6 [Note] Slave I mysql-bin.000001', position O thread exiting for channel', read up to log'4

III. 1593 error handling

Check to see if the server_id is duplicated, and you can see that the server_id of the two nodes is different.

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.01sec)

Mysql >

Mysql >

Mysql >

Mysql > exit

Check whether server-uuid is restarted, and you can see that the uuid of the two nodes is the same, mainly because my slave virtual machine is copied from 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

Modify the uuid of a slave node

[root@node3 mysql] # vi auto.cnf

[auto]

Server-uuid=cdf9834e-df88-11e5-99cd-080027701d16

~

~

Restart msyql services for master and slave

[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

Restart slave service Slave_IO_Running and Slave_SQL_Running are normal

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 >

The mysql log also shows normal startup.

2016-03-10T08:16:34.330158Z 1 [Note] Slave I 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 retry-time 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 go O thread killed during or after a reconnect done to recover from failed read

2016-03-10T08:17:32.160604Z 1 [Note] Slave I mysql-bin.000002', position O thread exiting for channel', read up to log '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 mysql-bin.000002' at position O thread for channel': connected to master 'backup@192.168.10.11:3306',replication started in log' 311

IV. Verification

After the master node creates a table, the slave node automatically synchronizes the newly created table

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 >

At this point, the study on "master and slave configuration of mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.