In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-07 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
1.1 introduction to the principle of MySQL master-slave replication
The master-slave replication of MySQL is an asynchronous replication process (although it generally feels real-time). The data will be replicated from one MySQL database (we call it Master) to another MySQL database (we call it Slave). The whole process of master-slave replication between Master and Slave is completed by three threads, including two threads (SQL thread and IO thread) on the Slave side. The other thread (Imax O thread) is on the Masterside.
In order to realize the master-slave replication of MySQL, the binlog recording function on Masterside must be turned on first, otherwise it cannot be realized. Because the whole replication process is actually Slave takes the binlog log from the Master side, and then performs the various SQL operations inspired in the acquired binlog log on the Slave in the same order.
To turn on the binlog recording feature of MySQL, you can add the parameter option "log-bin" to the mysql module of MySQL configuration file my.cnf (the parameter section marked by [mysql]). The details are as follows:
[mysqld]
Log-bin = / data/3306/mysql-bin
The following is a summary of the key points of the principle of MySQL master-slave replication.
◆ master-slave replication is asynchronous logical SQL statement-level replication.
When ◆ is replicated, the master library has one I / O thread, and the slave library has two threads, Igamot O and SQL threads.
The necessary condition for ◆ to realize master-slave replication is that the master library should turn on the record binlog function.
The server-id of ◆ as a replicated MySQL node cannot be the same.
The ◆ binlog file records only SQL statements that make changes to the data (changes to the contents of the autonomous database), not any query (select,show) statements.
Forget the database password
Mysqld_safe-defaults-file=/data/3306/my.cnf--skip-grant-table-user=mysql & run in the background
Then log in without entering a password
Mysql-uroot-p-S / data/3306/mysql.sock
Set the password after entering the database
Update mysql.user setpassword=password ('oldboy123') where user='root' and host='localhost'
Refresh permissions
Flush privileges
1.2 MySQL Master-Slave replication practice
Environment: multiple instanc
10.0.0.52 3306
10.0.0.52 3307
3306-> 3307 copy-> 3309
-> 3008 replication
3306 Master-> 3307 from
Architecture practice: 3306-> 3307
1.2.1 Open the main library binlog and configure server-id administrators
[root@db02 ~] # egrep-I "server-id | log-bin" / data/3306/my.cnf
Log-bin = / data/3306/mysql-bin
Server-id = 6
Restart the service
/ data/3306/mysql restart
Slave library
[root@db02 ~] # egrep-I "server-id | log-bin" / data/3307/my.cnf
# log-bin = / data/3307/mysql-bin
Server-id = 7
1.2.2 the main library creates rep users
Grant replication slave on *. * to 'rep'@'172.16.1.%'identified by' oldboy123'
Mysql > grant replication slave on *. * to'rep'@'172.16.1.%' identified by 'oldboy123'
Query OK, 0 rows affected (0.04 sec)
Mysql > select user,host from mysql.user
+-+ +
| | user | host |
+-+ +
| | root | 127.0.0.1 | |
| | rep | 172.16.1% | |
1.2.3 Export data from the main library
According to what we have seen, we can just take the backup at 00:00 today.
1. Lock the table flush table with read lock first
Mysql > flush table with read lock
Query OK, 0 rows affected (0.00 sec)
Mysql > show master status
+-+
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |
+-+
| | mysql-bin.000001 | 120 | |
+-+
1 row in set (0.00 sec)
two。 The main library is complete.
Have all three important orders
Mysqldump
Cp/tar
Xtrabackup
Getting the location point is the key sed-n '22p' all_2017-06-28.sql
[root@db02] # mysqldump-B-- master-data=2-- single-transaction-S / data/3306/mysql.sock-A | gzip > / data/backup/all_$ (date+%F) .sql.gz
[root@db02 ~] # ls-l / data/backup/
Total dosage 228
-rw-r--r-- 1 root root 178468 June 28 11:11 all_2017-06-28.sql.gz
3. Main library unlocked
Mysql > unlock table
Query OK, 0 rows affected (0.00 sec)
1.2.4 Import full data from the library
[root@db02 scripts] # cd/data/backup/
[root@db02 backup] # gzip-d all_2017-06-28.sql.gz
[root@db02 backup] # mysql-S / data/3307/mysql.sock start slave
Query OK, 0 rows affected (0.03 sec)
Mysql > show slave status\ G
The following results are displayed to prove the success of master-slave replication practice.
[root@db02 backup] # mysql-S / data/3307/mysql.sock-e "show slavestatus\ G" | egrep "_ Running | Behind_Master" | head-3
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
Seconds_Behind_Master: 0
#
◆ Slave_IO_Running: Yes, which is the state of the Yes thread. The thread reads the binlog log from the slave database to the master database and writes to the secondary log of the slave database. The status of Yes indicates that the IWeiO thread is working properly.
◆ Slave_SQL_Running: Yes, which is the SQL thread state. The SQL thread is responsible for reading the data in the relay log (relay-log) and translating it into SQL statements and applying it to the slave database. The state of Yes indicates that the SQL thread is working properly.
◆ Seconds_Behind_Master: 0, which is the number of seconds delayed by the slave database than the master database during replication. This parameter is very important, but a more accurate way to determine the master-slave delay in an enterprise is to write a timestamp in the master database, and then read the timestamp from the database to compare it with the current database time to determine whether it is delayed.
1.2.6 Log in to 3306 to view managed hosts
Mysql > showslave hosts
+-+
| | Server_id | Host | Port | Master_id | Slave_UUID | |
+-+
| | 7 | | 3307 | 6 | 295750c8-54c1-11e7-80dd-000c29fc02ee | |
| | 8 | | 3308 | 6 | 328e8c80-54c1-11e7-80dd-000c29fc02ee | |
+-+
2 rows in set (0.00 sec)
1.3 Summary of MySQL master-slave replication problems
Failure 1: the main library show master status; did not return a status result.
Mysql > show master status
Empty set (0.00 sec)
Answer: the reason for the above problem is that the binlog function switch is not turned on or does not work. The correct configuration results when binlog is enabled are as follows:
[root@db02 ~] # grep "log-bin" / data/3306/my.cnf
Log-bin = / data/3306/mysql-bin
[root@db02 ~] # mysql-uroot-poldboy123-S/data/3306/mysql.sock-e "showvariables like 'log_bin';"
Warning: Using a password on the command lineinterface can be insecure.
+-+ +
| | Variable_name | Value |
+-+ +
| | log_bin | ON |
+-+ +
Fault 2: error message "Last_IO_Error:Got fatal error 1236 from master when reading datafrom binary log:'Could notfind first log file name in binary log index file'" appears
Answer: the reason for the above failure is that there is an error caused by an extra space in the value of a parameter when executing the CHANGE MASTER command, as follows:
CHANGE MASTER TO
MASTER_HOST='172.16.1.52'
MASTER_PORT=3306
MASTER_USER='rep'
MASTER_PASSWORD='oldboy123'
MASTER_LOG_FILE=' mysql-bin.000001', # / opt/$ (date +% F). Sql.gz
The master-data=1 parameter adds the following statement to the backup data
-- position to start replication or point-in-timerecovery from
Change master tomaster_log_file='mysql-bin.000005',master_log_pos=107
2) find the opportunity to import the complete slave library on the slave library that needs to be copied, and the command is as follows:
Gzip-d 2017-07-08.sql.gz
Mysql-uroot-poldboy123-S / data/3308/mysql.sock
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.