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

This article is about how to achieve mysql master-slave replication and data backup and recovery. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The server does the following operations: > 1, the master and slave servers do the following operations respectively:

1.1. version consistent 1.2. initialize the table, start mysql 1.3 in the background, and change the password of root.

2. Modify the master server master:

# vi / etc/my.cnf [mysqld] log-bin=mysql-bin / / [must] enable binary log server-id=222 / / [must] unique ID of server. Default is 1. Generally, take the last paragraph of IP.

3. Modify the slave server slave:

# vi / etc/my.cnf [mysqld] log-bin=mysql-bin / / [not required] enable binary log server-id=226 / / [must] server unique ID. Default is 1. Generally take the last paragraph of IP.

4. Restart the mysql of two servers

Systemctl restart mysqld

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

# / usr/local/mysql/bin/mysql-uroot-pmttang mysql > GRANT REPLICATION SLAVE ON *. * to 'account' @ 'slave server's ip' identified by' password'

Generally, the root account is not used.% means that all clients may be connected. As long as the account number and password are correct, you can use the 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: after this Do not operate the master server MYSQL after the step Prevent the state value of the primary server from changing

7. Configure slave server Slave:

Be careful not to disconnect mysql > change master to master_host='192.168.71.128',master_user='root',master_password='root',master_log_file='mysql-bin.000004',master_log_pos=308; / /. 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 statusG * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.222 / / main server address Master_User: mysync / / authorized account name, try to avoid using root Master_Port: 3306 / / database port Some versions do not have this line Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 600 / / # synchronous read binary log location, 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 state must be YES Slave_SQL_Running: Yes / / this state 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; 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 | / / Icomm 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. Finish: 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.

MySQL master-slave synchronization failure-Slave_SQL_Running: No

Symptom: enter the slave server and run:

Mysql > show slave statusG. Relay_Log_File: localhost-relay-bin.000535 Relay_Log_Pos: 21795072 Relay_Master_Log_File: localhost-bin.000094 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB:.

Solution 1.

First stop the Slave service:

Mysql > stop slave

Check the host status on the master server: record the values corresponding to File and Position and enter master

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- +-- -+-+ | localhost-bin.000094 | 33622483 | +-+ 1 row in set (0.00 sec)

Then perform a manual synchronization on the slave server:

Mysql > change master to master_log_file='loccalhost-bin.000094',master_log_pos=33622483;mysql > start slave;// enable synchronization

Check the status again to see if it is normal.

Mysql > show slave statusG** 1. Row *. Master_Log_File: localhost-bin.000094 Read_Master_Log_Pos: 33768775 Relay_Log_File: localhost-relay-bin.000537 Relay_Log_Pos: 1094034 Relay_Master_Log_File: localhost-bin.000094 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB:

Note: manual synchronization needs to stop the write operation of the main library!

Solution II.

Mysql > stop slave;mysql > set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;mysql > start slave

Description: Slave_IO_Running: connect to the main library, and read the log of the main library to the local, generate a local log file Slave_SQL_Running: read the local log file, and execute the SQL command in the log.

Data backup:

Format: mysqldump-h link ip-P (uppercase) port-u user name-p password database name > d:XX.sql (Lujin)

Example: mysqldump-uroot-p8888 htgl > d:htgl.sql if an error occurs Warning: Using a password on the command line interface can be insecure. Then you need to modify the configuration file:

Vim / etc/mysql/ my.cnf[mysqldump] user=your_backup_user_namepassword=your_backup_password

After modifying the configuration file and restarting MyQL, you only need to execute the mysqldump script. User name and password information is not required in the backup script. Example: mysqldump htql > d:htql.sql

Data recovery:

1. System command line

Format: mysql-h link ip-P (uppercase) port-u user name-p password database name < d:XX.sql (Lujin)

Mysql-uusername-ppassword db1 user db1;source tb1_tb2.sql

3. The method of restoring the whole database:

Mysql-u b_user-h 101.3.20.33-paired Happy password`-P3306 < all_database.sql

Thank you for reading! On "how to achieve mysql master-slave replication and data backup recovery" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

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.