Mysql master and slave copy of ES2en ES3en actual operation case
- 2020-06-19 11:49:13
- OfStack
In this section, let's look at how user authorization and master-slave replication occur in Mysql
Here's a look at the advantages of MASTer-slave replication in Mysql:
1. If there is a problem with the primary server, you can quickly switch to the service provided by the secondary server
2. Query can be performed on the slave server to reduce the access pressure of the master server
3. A backup can be performed on the slave server to avoid affecting the primary server's services during the backup
Note: generally, only the data with infrequent update or the data with low real-time requirement can be searched from the slave server, while the data with high real-time requirement still needs to be obtained from the master database
Here we first complete user authorization in order to give the slave server enough privileges to remotely log on to Mysql on the master server
I'm going to assume here
IP for the primary server is: 192.168.10.1
IP for slave server is: 192.168.10.2
Mysql grant User authorization
View Mysql's user table
msyql> mysql -uroot -p123123;
msyql> select user, host, password from mysql.user;
The results are as follows:
+------------------+-----------+-------------------------------------------+
| user | host | password |
+------------------+-----------+-------------------------------------------+
| root | localhost | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| root | 127.0.0.1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+------------------+-----------+-------------------------------------------+As seen in the table above, root users can only log in to Mysql from native, i.e. from localhost or 127.0.0.1
Now add authorized users through the grant command
msyql> ? grant // To view grant Detailed usage of
msyql> grant all on *.* to user1@192.168.10.2 identified by "123456"; // *.* = All the databases . All the tables
// or
msyql> grant replication slave on *.* to 'user2'@'192.168.10.%' identified by "123456"; // % Stands for wildcard The grant command gave the user user1 from 192.168.10.2 permission to log in remotely, 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 |
+------------------+--------------+-------------------------------------------+At this point, you can access 10.1 Mysql on the 192.168.10.2 machine, as follows:
msyql> mysql -uuser1 -p123456 -h192.168.10.1;
Mysql bin - log log
Turn on the bin-ES58en 2 base log, which holds all additions and deletions for data recovery or synchronization
Modify the primary server mysql configuration file:
shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
/********** my.cnf **********/
[mysqld]
# Turn on the slow query log, Record queries that are too long sql Statement to facilitate optimization
log_slow_queries = /var/log/mysql/mysql-slow.log
# open bin-log The log
log-bin = /var/log/msyql/mysql-bin.logRestart the Mysql service when the addition is complete
shawn@Shawn:~$ sudo /etc/init.d/mysql restartYou can now check to see if the ES69en-ES70en log is enabled successfully by following the command
mysql> show variables like "%log_%";
| log_bin | ON |
| log_slow_queries | ON |If it shows ON, you can see the mysql-ES79en.000001 base 2 file in the /var/log/mysql/ folder
Actions related to the bin-ES83en log:
mysql> flush logs ; The latest ES87en-ES88en log is added at this point
mysql> show master status;View the last bin-ES93en log as follows:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 107 | | |
+------------------+----------+--------------+------------------+
mysql> show master logs;View all ES97en-ES98en logs as follows:
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 4340 |
| mysql-bin.000002 | 107 |
+------------------+-----------+
mysql> reset master;Empty all bin-ES103en logs
shawn@Shawn:~$ mysqlbinlog /var/log/mysql/mysql-bin.000001 | moreView the ES107en-ES108en log content
# If there is a character set problem, it can be executed :
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 mysql-bin.000002 All operations to test In the database
shawn@Shawn:~$ mysqlbinlog /var/log/mysql/mysql-bin.000002 --start-position="193" --stop-position="398" | mysql -uroot -p123123 test;
restore mysql-bin.000002 The operation specified in (position) to test In the database Mysql master-slave copy-data synchronization
At this step, first make sure that the Mysql user authorization has been completed and that the Mysql ES118en-ES119en log has been opened successfully
And make sure that server-id is unique to each server
Modify the mysql configuration file for the primary server (192.168.10.1) again:
shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
/********** my.cnf **********/
# cancel server-id Annotation symbols
server-id = 1
/****************************/
# restart Mysql service
shawn@Shawn:~$ sudo /etc/init.d/mysql restart At this point, the configuration of the primary server is complete and simple
What we mainly do this time is to let the slave server synchronize the data of the master server. What we synchronize is all the additions and deletions made to the master service in the future. However, a large amount of data in the existing master server must be manually synchronized to the slave server first.
# empty 1 To the primary server bin-log The log, (optional : Insurance operation, To prevent the master-slave bin-log Log confusion)
mysql> reset master;
# Then back up the existing one in the primary server test The database
shawn@Shawn:~$ mysqldump -uroot -p123123 test -l -F > /tmp/test.sql;
-F = flush logs, Generate a new log file, including bin-log The log
-l = lock Database, To prevent data from being written at export, Automatically unlocks after completion
# Transfer the file to the slave server when complete
shawn@Shawn:~$ scp /tmp/test.sql 192.168.10.2:/tmp/
# Then query to make sure 1 The slave server has been successfully authorized
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'With that done, let's now import the existing data from the server (192.168.10.2) :
# empty 1 Down from the server bin-log The log, (optional : Insurance operation)
mysql> reset master;
# Then import the existing data in the primary server
shawn@Shawn:~$ mysqldump -uroot -p123123 test -v -f < /tmp/test.sql;-ES138en = See the import details
-ES140en = when an error occurs in the middle, skip can pass and continue with the following statement
You can also import with the source command
Ok, so far the existing data from the master server (192.168.10.1) and the slave server (192.168.10.2) has been manually synchronized successfully
Next modify the mysql configuration file from the server (192.168.10.2) :
shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
/********** my.cnf **********/
# cancel server-id Annotation symbols , And modify the value
server-id = 2
# cancel master-host Annotation symbols , And modify the value
master-host = 192.168.10.1
# cancel master-user Annotation symbols , And modify the value
master-user = user1
# cancel master-password Annotation symbols , And modify the value
master-password = 123456
# cancel master-port Annotation symbols , And modify the value , The primary server default port number is : 3306
master-port = 3306
/****************************/
# restart Mysql service
shawn@Shawn:~$ sudo /etc/init.d/mysql restartWhen configuration file modification is complete, log in to your Mysql from the server instead of remotely logging into the main server (192.168.10.1)
+------------------+-----------+-------------------------------------------+
| user | host | password |
+------------------+-----------+-------------------------------------------+
| root | localhost | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| root | 127.0.0.1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+------------------+-----------+-------------------------------------------+Slave_IO_Running if it is Yes, then the synchronization from the primary server to the bin-ES160en logs is successful
Slave_SQL_Running if it is Yes, the SQL statement in the bin-ES167en log was successfully executed
The value of Master_Log_FIle and Read_Master_Log_Pos at this point should correspond to the value of the show master status command in the primary server
The 60 in Connect_Retry represents going to the primary server every 60 seconds to synchronize the bin-ES184en logs
OK, if you see those two key Yes, then you can test it, insert new data on the master server, and then go to the slave server, and hopefully, you'll be excited 1 that the data has been synchronized
Here's another frequently used command:
+------------------+-----------+-------------------------------------------+
| user | host | password |
+------------------+-----------+-------------------------------------------+
| root | localhost | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| root | 127.0.0.1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+------------------+-----------+-------------------------------------------+It also says 1 common error in operation:
Problem: Unable to synchronize from database
The value of Slave_SQL_Running is NO, or Seconds_Bebind_Master is Null
The reason:
1. The program may have written on slave
2. It is also possible that the transaction was rolled back after the slave machine was restarted
Solution 1:
msyql> ? grant // To view grant Detailed usage of
msyql> grant all on *.* to user1@192.168.10.2 identified by "123456"; // *.* = All the databases . All the tables
// or
msyql> grant replication slave on *.* to 'user2'@'192.168.10.%' identified by "123456"; // % Stands for wildcard Solution 2:
msyql> stop slave;
# View the current on the primary server bin-log Log names and offsets
msyql> show master status;
# Gets the following :
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 286 | | |
+------------------+----------+--------------+------------------+
# Manual synchronization is then performed to the slave server
msyql> change master to
-> 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 via show slave status:
It is normal if the value of Slave_SQL_Running changes to Yes and the value of Seconds_Bebind_Master is 0
Well, the above is my own summary in the operation of 1 some content, if there are better Suggestions, welcome to leave a comment 1 to discuss
By the way, I used Ubuntu 12.04