|
Now the MySQL database in the production environment has one master and one slave. Due to the increasing business volume, another slave database is added. The premise is that it cannot affect online business use, which means that the MySQL service cannot be restarted. In order to avoid other situations, choose to operate during the low peak period of website traffic.
Generally there are two ways to add a slave database online. One is to back up the main database through mysqldump and restore to the slave database. Mysqldump is a logical backup. When the amount of data is large, the backup speed will be very slow and the table lock time will be very long. . The other is to back up the main database through the xtrabackup tool and restore it to the slave database. xtrabackup is a physical backup, which has fast backup speed and does not lock tables. Why not lock the table? Because it monitors the main database log, if there is updated data, it will be written to a file first, and then returned to the backup file to maintain data consistency.
Server information:
Main database: 192.168.18.212 (original)
Slave database 1: 192.168.18.213 (original)
Slave database 2: 192.168.18.214 (new)
Database version: MySQL5. 5
Storage engine: Innodb
Test library name: weibo
1. mysqldump method
MySQL master-slave is based on binlog log, so binlog must be turned on after installing the database. The advantage of this is that on the one hand, you can use binlog to restore the database, and on the other hand, you can prepare for the master and slave.
The original main library configuration parameters are as follows:
# vi my.cnf
server-id = 1 #The id must be unique
log-bin = mysql-bin #Enable binlog log
auto-increment-increment= 1 #In Ubuntu system, MySQL 5.5 and later has defaulted to 1
auto-increment-offset = 1
slave-skip-errors =all #Skip errors that occur in master-slave replication
1. Create a synchronization account for the master library mysql> grant all on*.* to 'sync'@'192.168.18.%' identified by 'sync';
2. Configure MySQL from the library
# vi my.cnf
server-id = 3 # This setting 3
log-bin = mysql-bin #Turn on binlog log
auto-increment-increment= 1 #These two parameters have defaulted to 1
auto-increment-offset in the Ubuntu system after MySQL5.5 = 1
slave-skip-errors =all #Skip errors in master-slave replication
3. Back up the master library
# mysqldump -uroot -p123--routines --single_transaction --master-data=2 -- databases weibo >weibo.sql
Parameter description:
--routines: Export stored procedures and functions
--single_transaction: Set the transaction isolation status at the beginning of the export, and use a consistent snapshot to start the transaction, and then unlock tables; while lock-tables locks a table and prevents write operations until the dump is completed.
--master-data: The default is equal to 1, and the dump starting (change master to) binlog point and pos value are written into the result. When equal to 2, the changemaster to is written into the result and commented.
4. Copy the backup library to the slave library
# scp weibo.sqlroot@192.168.18.214:/home/root
5. Create the test_tb table in the main library to simulate new data in the database, weibo.sql is No
mysql> create tabletest_tb(id int,name varchar(30));
6. Import the backup library from the library
# mysql -uroot -p123 -e'create database weibo;'
# mysql -uroot -p123weibo < weibo.sql 7. Check the binlog and pos values in the backup file weibo.sql # head -25 weibo.sql -- CHANGE MASTER TOMASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107 ; #About 22 lines 8. Synchronize the slave library settings from this log point and start mysql> change masterto master_host='192.168.18.212',
-> master_user='sync',
-> master_password='sync',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=107;
mysql> start slave;
mysql> show slavestatusG; ERROR 2006 (HY000 ): MySQLserver has gone away
No connection. Trying toreconnect...
Connection id: 90
Current database: ***NONE ***
******************************1. row * ****************************
SLAVE_IO_STATE: WAITING FORMASTER to Send Event
Master_host: 192.168.18.212
Master_user: Sync
Master_port ) Relay_Log_File:mysqld-relay-bin.000003
Relay_Log_Pos: 504
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yesl slave_sql_running: yes
......
9. Seeing the tables in the weibo library
can see that the IO and SQL threads are yes, indicating that the main configuration is successful.
mysql> show tables;
+--------------------------+
| Tables_in_weibo |
+---- -----------------------+
| test_tb |
I found that the test_tb table that was simulated just now has been synchronized!
2. xtrabackup method (recommended)
Do experiments based on the above configuration, first delete the slave configuration:
mysql> stopslave; mysql> show slavestatusG; #Check the slave status again, you can see that both the IO and SQL threads are NOmysql> drop databaseweibo; #Delete the weibo libraryAt this time, the slave library is now the same as the newly installed one, move on! 1. Use xtrabackup to back up the main library# innobackupex --user=root --password=123 ./Generate a backup directory named after time: 2015-07-01_16-49-43# ll 2015-07-01_16-49-43/total 18480drwxr-xr-x 5 rootroot 4096 Jul 1 16:49 ./drwx------ 4 rootroot 4096 Jul 1 16:49 .. /-rw-r--r-- 1 rootroot 188 Jul 1 16:49 backup-my.cnf-rw-r----- 1 root root18874368 Jul 1 16:49 ibdata1drwxr-xr -x 2 rootroot 4096 Jul 1 16:49 mysql/drwxr-xr-x 2 rootroot 4096 Jul 1 16:49 performance_schema/drwxr-xr-x 2 rootroot 12288 Jul 1 16:49 weibo/ -rw -r--r-- 1 rootroot 21 Jul 1 16:49 xtrabackup_binlog_info-rw-r----- 1 rootroot 89 Jul 1 16:49 Jul 1 16:49 xtrabackup_info-rw-r----- 1 rootroot 2560 Jul 1 16:49 xtrabackup_logfile2. Copy the backup directory to the slave library# scp -r2015-07-01_16-49 -43 root@192.168.18.214:/home/root3. Stop the MySQL service from the database, delete the datadir directory, and rename the backup directory to the datadir directory# sudo rm -rf/var/lib/mysql /# sudo mv2015-07-01_16-49-43/ /var/lib/mysql# sudo chown mysql.mysql -R /var/lib/mysql# sudo /etc/init.d/mysqlstartmysql 8832 1 0 16:55 ? 00:00:00 /usr/sbin/mysqld
4. Create the test_tb2 table in the main database and simulate new data in the database
mysql> create tabletest_tb2(id int,name varchar(30 );
5. From the backup directory, Xtrabackup_info file obtained Binlog and POS positions = =
tool_name = innobackupex
tool_command =--user=root --password=... ./
tool_version =1.5.1-xtrabackup
ibbackup_version =xtrabackup version 2.2.11 based on MySQL server 5.6.24 Linux (x86_64) (revisionid: )
server_version =5.5.43-0ubuntu0.12.04.1-log
start_time = 2015-07-0116:49:43
end_time = 2015-07-0116:49:46
lock_time = 1
binlog_pos = filename'mysql-bin.000001', position 429 #This position
innodb_from_lsn = 0
innodb_to_lsn = 1598188
partial = N
incremental = N
form at = file
compact = N
compressed = N
6. Synchronize the slave library settings from this log point and start
mysql> change masterto master_host='192.168.18.212',
-> master_user='sync',
-> master_password= 'sync',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=429;
mysql> start slave;
mysql> show slavestatusG;
****** *********************1. row *************************** *V SLAVE_IO_STATE: WAITING FORMASTER to Send Event
Master_host: 192.168.18.212
Master_user: Sync
Master_Port: 3306
bin.000001a Read_Master_LOG_POS: 539
Relay_log_file: mysql-bin.000002
Slave_SQL_Running: Yes
......
7. View the weibo library from the library In the table
, you can see that both the IO and SQL threads are YES, indicating that the master-slave configuration is successful.
mysql> show tables;
+--------------------------+
| Tables_in_weibo |
+---- -----------------------+
| test_tb |
| test_tb2 |
I found that the test_tb2 table created by the simulation just now has been synchronized.
Get Brothers IT Education’s original Linux operation and maintenance engineer video/detailed Linux tutorial for free. For details, please contact the official website customer service: http://www.lampbrother.net/linux/
Learn PHP, Linux, HTML5, UI, Android and other videos Tutorial (courseware + notes + video)! Contact Q2430675018
Participate in the event to receive the original video tutorial CD collection of Brothers: http://www.lampbrother.net/newcd.html
|
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
