Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about how to set up the master and slave in mysql. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. Make sure the master database is exactly the same as the slave database.

For example, if the database of an in the main database has a table of bforce crecrine d, then there should be a database of an engraved out of a mold and a table of bMagec d in the database.

2. Create a synchronization account on the main database.

GRANT REPLICATION SLAVE ON *. * to 'djy'@'%' identified by' 12345678'

Djy: is the newly created user name

12345678: is the password for the newly created user name

For the detailed explanation of the above orders, it is best for Baidu to write too much to make it more unclear.

3. Configure the my.ini of the master database (because it is under window, it is my.ini, not my.cnf).

# vi / etc/my.cnf

[mysqld]

Log-bin=mysql-bin / / [must] enable binary logging

Server-id=222 / / [required] the server has a unique ID. The default is 1. Generally, the last segment of IP is taken.

Other extended configuration items (no default full database master-slave backup is configured below):

Binlog-do-db=djydjy # the name of the database that needs to be backed up. If you back up multiple databases, you can set this option repeatedly.

Binlog-ignore-db=mysql # does not need the database name to be backed up. If you back up multiple databases, you can set this option repeatedly.

The parameter log-slave-updates=1 # must be added, otherwise the updated records will not be added to the binary file

Slave-skip-errors=all # skips the error and continues with the copy operation (optional)

Expire_logs_days=2 # sets the number of days log is retained

Configure the my.cnf from the database.

# vi / etc/my.cnf

[mysqld]

# log-bin=mysql-bin / / [not required] enable binary logging

Server-id=226 / / [required] the server has a unique ID. The default is 1. Generally, the last segment of IP is taken.

The parameter log-slave-updates=1 # must be added, otherwise the updated records will not be added to the binary file

Slave-skip-errors=all # skips the error and continues with the copy operation (optional)

Expire_logs_days=2 # sets the number of days log is retained

4. Restart the mysql of two servers

/ etc/init.d/mysql restart

5. Establish an account on the master server and authorize slave:

# / usr/local/mysql/bin/mysql-uroot-pmttang

Mysql > GRANT REPLICATION SLAVE ON *. * to 'djy'@'%' identified by' 12345678; / / generally, no root account is used. "%" means that all clients may be connected. As long as the account number and password are correct, you can use specific client IP here, such as 192.168.145.226, to enhance security.

6. Log in to the mysql of the master server and query the status of master

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000004 | 308 |

+-+

1 row in set (0.00 sec)

Note: do not operate the master server MYSQL after performing this step to prevent the status value of the master server from changing

7. Configure slave server Slave:

Be careful not to disconnect mysql > change master to master_host='10.20.4.10',master_user='djy',master_password='12345678',master_log_file='mysql-bin.000001',master_log_pos=154; / /. There are no single quotation marks around 308 digits.

Mysql > start slave; / / start the copy from server function

8. Check the status of replication from the server:

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.2.222 / / Master server address

Master_User: mysync / / authorized account name, avoid using root as much as possible

Master_Port: 3306 / / database port, which is not available in some versions

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 600 / / # the location where binary logs are read synchronously, which is greater than or equal to Exec_Master_Log_Pos

Relay_Log_File: ddte-relay-bin.000003

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes / / this status must be YES

Slave_SQL_Running: Yes / / this status must be YES

.

Note: the Slave_IO and Slave_SQL processes must be running normally, that is, the YES state, otherwise they are all in the wrong state (for example, one of the NO is wrong).

The configuration of the master and slave server is completed in the above operation process.

9. Master-slave server test:

The master server Mysql, set up the database, and insert a piece of data into the library by creating a table:

Mysql > create database hi_db

Query OK, 1 row affected (0.00 sec)

Mysql > use hi_db

Database changed

Mysql > create table hi_tb (id int (3), name char (10))

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into hi_tb values (001)

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | hi_db |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Query from server Mysql:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | hi_db | / / Itemm here, you see? |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > use hi_db

Database changed

Mysql > select * from hi_tb; / / View the specific data added on the master server

+-+ +

| | id | name |

+-+ +

| | 1 | bobu |

+-+ +

1 row in set (0.00 sec)

10. Complete:

Write a shell script to monitor two yes (Slave_IO and Slave_SQL processes) of slave with nagios. If you find that there is only one or zero yes, it indicates that there is something wrong with the master and slave. Send a text message alarm.

11. Simplify orders and debug

GRANT REPLICATION SLAVE ON *. * to 'djy'@'%' identified by' 12345678'

Show master status

Change master to master_host='10.20.4.33',master_user='djy',master_password='12345678',master_log_file='mysql-bin.000003',master_log_pos=154

Start slave

Show slave status\ G

View all users in the MYSQL database

Mysql > SELECT DISTINCT CONCAT ('User:'', user,'''@''',host,''';') AS query FROM mysql.user

Start slave; error: ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

Policy: use reset slave all; to clear all replication information

MySQL error: master and slave have equal MySQL server UUIDs

Strategy:

# vi / main/mysql/data/auto.cnf

[auto]

Server-uuid=f4863bcf-1d28-11e4-8657-000c29eec7bf

# you can change one or two values at will, or rewrite one according to the above format

twelve。 Skip errors, check for errors

Mysql master-slave replication often encounters errors that lead to interruption of server replication. At this time, manual intervention is generally required to skip the error before continuing.

There are two ways to skip errors:

a. Skip the specified number of transactions:

Mysql > slave stop

Mysql > SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 # Skip a transaction

Mysql > slave start

b. Modify the configuration file of mysql to skip all errors or specified types of errors through the slave_skip_errors parameter

Vi / etc/my.cnf

[mysqld]

# slave-skip-errors=1062,1053,1146 # Skip error of specified error no type

# slave-skip-errors=all # skip all errors

c. Restore deleted data

According to the error message, find the data event SQL with mysqlbinlog and execute it manually in reverse. For example, delete is changed to insert.

In this case, the location of this event in the primary server Master binlog is mysql-bin.000003, end_log_pos 440267874.

1) use mysqlbinlog tool to find out 440267874 events

/ usr/local/mysql-5.6.30/bin/mysqlbinlog-- base64-output=decode-rows-vv mysql-bin.000003 | grep-A 20 '440267874'

Or / usr/local/mysql-5.6.30/bin/mysqlbinlog-base64-output=decode-rows-vv mysql-bin.000003-stop-position=440267874 | tail-20

Or usr/local/mysql-5.6.30/bin/mysqlbinlog-- base64-output=decode-rows-vv mysql-bin.000003 > decode.log

(or add the parameter-d,-- database=name to further filter)

# 160923 20:01:27 server id 1223307 end_log_pos 440267874 CRC32 0x134b2cbc Delete_rows: table id 319 flags: STMT_END_F### DELETE FROM `db_ 99ducj`.`tbuservcbgolog` # WHERE### @ 1 million 10561502 / * INT meta=0 nullable=0 is_null=0 * / # # @ 2 million 1683955 / * INT meta=0 nullable=0 is_null=0 * / # @ 3room90003 / * INT meta=0 nullable=0 is_null=0 * / # # @ 4room0 / * INT meta=0 nullable=0 is_null=0 * / # @ 5 minutes 2016-09-23 17 6=NULL 026=NULL 24'/ * DATETIME (0) meta=0 nullable=1 is_null=0 * / # @ 6=NULL / * DATETIME (0) meta=0 nullable=1 is_null=1 * / # at 440267874 this is how to set the master and slave in the mysql shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.