Mysql master-slave replication (master-slave) actual operation case _ MySQL

In this section, we will learn how to grant user authorization and master-slave replication in Mysql.

The following describes the advantages of Mysql master-slave replication:

1. if a problem occurs on the master server, you can quickly switch to the service provided by the slave server.
2. query operations can be performed on the slave server to reduce the access pressure on the master server.
3. backup can be performed on the slave server to avoid affecting the services of the master server during the backup.
Note that only data that is not frequently updated or that does not have high real-time requirements can be queried from the server. data with high real-time requirements still needs to be obtained from the master database.

Here, we first need to complete user authorization, in order to give the slave server sufficient permissions to remotely log on to the master server's Mysql

Here I assume that
The IP address of the master server is 192.168.10.1.
The slave server IP address is 192.168.10.2.

Mysql grant user authorization

View the Mysql User table
The code is as follows:
Msyql> mysql-uroot-p123123;
Msyql> select user, host, password from mysql. user;

The result is as follows:
The code is as follows: + ------------------ + ----------- + --------------------------------------- +
| User | host | password |
+ ------------------ + ----------- + ------------------------------------------- +
| Root | localhost | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| Root | 127.0.0.1 | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+ ------------------ + ----------- + ------------------------------------------- +
From the above table, we can see that the root user can only log on to Mysql from the local machine, that is, from localhost or 127.0.0.1.

Now, use the grant command to add authorized users.
The code is as follows:
Msyql>? Grant // view detailed usage of grant

Msyql> grant all on *. * to user1@192.168.10.2 identified by "123456"; // *. * = all databases. all tables
// Or
Msyql> grant replication slave on *. * to 'user2' @ '192. 192.% 'identified by "168.10"; // % represents a wildcard
The grant command is used to grant user1 permissions to users from 192.168.10.2 and allow them to log on remotely, as shown below:
The code is as follows:
+ ------------------ + -------------- + ------------------------------------------- +
| User | host | password |
+ ------------------ + -------------- + ------------------------------------------- +
| Root | localhost | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| Root | 127.0.0.1 | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| User1 | 192.168.10.2 | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| User2 | 192.168.10.% | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+ ------------------ + -------------- + ------------------------------------------- +
In this case, you can access Mysql 10.1 on the host 192.168.10.2, as shown below:
The code is as follows:
Msyql> mysql-uuser1-p123456-h192.168.10.1;

Mysql bin-log

Enable the binlog binary log, which saves all added, deleted, and modified operations for data recovery or synchronization.

Modify the mysql configuration file of the master server:
The code is as follows: shawn @ Shawn :~ $ Sudo vi/etc/mysql/my. cnf;

/********** My. cnf **********/
[Mysqld]

# Enable slow query logs to record SQL statements that are too long for query, so as to facilitate optimization
Log_slow_queries =/var/log/mysql/mysql-slow.log.

# Enable bin-log
Log-bin =/var/log/msyql/mysql-bin.log
Restart the Mysql service after adding the service.
The code is as follows:
Shawn @ Shawn :~ $ Sudo/etc/init. d/mysql restart
Now you can run the following command to check whether the bin-log is successfully enabled:
The code is as follows:
Mysql> show variables like "% log _ % ";

| Log_bin | ON |
| Log_slow_queries | ON |
If displayed as ON, you can see the mysql-bin.000001 binaries in the/var/log/mysql/folder

Operations related to bin-log:
The code is as follows:
Mysql> flush logs;
At this time, a new bin-log will be added.
The code is as follows:
Mysql> show master status;
View the last bin-log as follows:
The code is as follows:
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000002 | 107 |
+ ------------------ + ---------- + -------------- + ------------------ +

Mysql> show master logs;
View all bin-log logs as follows:
The code is as follows:
+ ------------------ + ----------- +
| Log_name | File_size |
+ ------------------ + ----------- +
| Mysql-bin.000001 | 4340 |
| Mysql-bin.000002 | 107 |
+ ------------------ + ----------- +

Mysql> reset master;
Clear all bin-log
The code is as follows:
Shawn @ Shawn :~ $ Mysqlbinlog/var/log/mysql/mysql-bin.000001 | more
View the bin-log content
The code is as follows:
# If there is a character set problem, run the following command:
Shawn @ Shawn :~ $ Mysqlbinlog -- no-defaults/var/log/mysql/mysql-bin.000001

Shawn @ Shawn :~ $ Mysqlbinlog/var/log/mysql/mysql-bin.000002 | mysql-uroot-p123123 test;
Restore all actions in the mysql-bin.000002 to the test database

Shawn @ Shawn :~ $ Mysqlbinlog/var/log/mysql/mysql-bin.000002 -- start-position = "193" -- stop-position = "398" | mysql-uroot-p123123 test;
Restore the operation (position) specified in the mysql-bin.000002 to the test database

Mysql master-slave replication-data synchronization

In this step, ensure that the Mysql User authorization is complete and the Mysql bin-log is enabled successfully.
Make sure that the server-id of each server is unique.

Modify the mysql configuration file of the master server (192.168.10.1) again:
The code is as follows:
Shawn @ Shawn :~ $ Sudo vi/etc/mysql/my. cnf;

/********** My. cnf **********/
# Cancel the server-id Annotator
Server-id = 1
/****************************/

# Restart the Mysql service
Shawn @ Shawn :~ $ Sudo/etc/init. d/mysql restart
By now, the configuration of the master server has been completed, which is very simple.

This time, we mainly want to synchronize the data of the master server from the slave server, and synchronize all future additions, deletions, and modifications to the master service, however, a large amount of data in the existing master server must be manually synchronized to the slave server as follows:
The code is as follows:
# Clear the bin-log of the master server (optional: insurance operation to prevent confusion of the master/slave bin-log)
Mysql> reset master;

# Back up and export the existing test database in the master server
Shawn @ Shawn :~ $ Mysqldump-uroot-p123123 test-l-F>/tmp/test. SQL;

-F = flush logs to generate a new log file, including the bin-log
-L = lock the database to prevent data from being written during Export. the database is automatically unlocked after export.

# Transfer the file to the slave server
Shawn @ Shawn :~ $ Scp/tmp/test. SQL 192.168.10.2:/tmp/

# Check whether the slave server has been granted permissions
Mysql> show grants for user1@192.168.10.2/G

* *************************** 1. row ***************************
Grants for user1@192.168.10.2:
Grant all privileges on *. * TO 'user1' @ '192. 168.10.2'
Identified by password '* 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
Now, we can import the existing data to the slave server (192.168.10.2:
The code is as follows:
# Clear the bin-log of the slave server (optional: insurance operation)
Mysql> reset master;

# Then import the existing data in the master server
Shawn @ Shawn :~ $ Mysqldump-uroot-p123123 test-v-f </tmp/test. SQL;

-V = View import details
-F = when an error occurs in the middle, you can use the skip to continue executing the following statement.
Of course, you can also use the source command to import
Now, the existing data of the master server (192.168.10.1) and slave server (192.168.10.2) has been manually synchronized.

Next, modify the mysql configuration file of the slave server (192.168.10.2:
The code is as follows:
Shawn @ Shawn :~ $ Sudo vi/etc/mysql/my. cnf;

/********** My. cnf **********/
# Cancel the server-id Annotator and modify the value
Server-id = 2

# Cancel the comments on the master-host and modify the value.
Master-host = 192.168.10.1

# Cancel the comment symbol of master-user and modify the value
Master-user = user1

# Cancel the comment symbol of master-password and modify the value
Masters-password = 123456

# Cancel the comment symbol of the master-port and modify the value. the default port number of the master server is 3306.
Master-port = 3306
/****************************/

# Restart the Mysql service
Shawn @ Shawn :~ $ Sudo/etc/init. d/mysql restart
After the configuration file is modified, log on to your Mysql instance from the server instead of logging on to the master server remotely (192.168.10.1)
The code is as follows:
# Log on to your Mysql instance from the server
Msyql> mysql-uroot-p123123;

# Check whether synchronization has been obtained
Msyql> show slave status/G

* *************************** 1. row ***************************
Connect_Retry: 60
Master_Log_FIle: mysql-bin.000002
Read_Master_Log_Pos: 106
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Slave_IO_Running, if Yes, indicates that the log is successfully synchronized from the master server to the bin-log.
If Slave_ SQL _Running is Yes, the SQL statement in the bin-log is successfully executed.
The Master_Log_FIle and Read_Master_Log_Pos values should correspond to the value of the show master status command on the master server.
60 in Connect_Retry indicates that the bin-log is synchronized to the master server every 60 seconds.

OK, if you see the two key Yes, you can test it, insert new data on the master server, and then view it on the server. no surprise, you will be excited that the data has been synchronized.

Here we will talk about other frequently used commands:
The code is as follows:
# Start the replication thread
Msyql> start slave

# Stop the replication thread
Msyql> stop slave

# Dynamically changing the configuration to the master server
Msyql> change master

# View processes running from a database
Msyql> show processlist
Common errors in operations are also described here:

Problem: Slave Database cannot be synchronized
The Slave_ SQL _Running value is NO, or the Seconds_Bebind_Master value is Null.

Cause:
1. the program may write data on slave.
2. it may also be caused by transaction rollback after the slave machine is restarted.

Solution 1:
The code is as follows:
Msyql> stop slave;

Msyql> set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1;

Msyql> start slave;
Solution 2:
The code is as follows:
Msyql> stop slave;

# View the current bin-log name and offset on the master server
Msyql> show master status;

# Obtain the following content:
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000005 | 286 |
+ ------------------ + ---------- + -------------- + ------------------ +

# Then execute Manual synchronization on the slave server
Msyql> change master
-> Master_host = "192.168.10.1"
-> Master_user = "user1"
-> Master_password = "123456"
-> Master_post = 3306.
-> Master_log_file = "mysql-bin.000005"
-> Master_log_pos = 286;

Msyql> start slave;
Check again through show slave status:
If the value of Slave_ SQL _Running changes to Yes and the value of Seconds_Bebind_Master is 0, it is normal.

Well, the above are some of the content I have summarized in my operations. if you have better suggestions, please leave a message to discuss it together.
By the way, I use Ubuntu 12.04.