Do not stop the MySQL service by adding two ways from the library

Now the production environment MySQL database is a primary one from, because the traffic volume of access is increasing, so add another from the library. The premise is not to affect the online business use, that is, can not restart the MySQL service, in order to avoid other situations, choose the site traffic low peak time period operation. General online increase from the library there are two ways, one is through the mysqldump Backup main Library, restore to from the library, mysqldump is a logical backup, the data volume is large, the backup speed will be very slow, lock the table time will be very long. Another is to back up the main library through the Xtrabackup tool, restore to from the library, Xtrabackup is the physical backup, the backup speed is fast, does not lock the table. Why not lock the table? Because the main library log is monitored by itself, if there is updated data, it is written to a file and then returned to the backup file to maintain data consistency. Server information: Main Library: 192.168.18.212 (original) from Library 1:192.168.18.213 (Legacy) 2:192.168.18.214 from library (new) database version: MySQL5.5 storage Engine: InnoDB Test Library Name: WeiboFirst, Mysqldump wayMySQL master-Slave is based on the Binlog log, so after installing the database will be opened Binlog. The advantage is that on the one hand you can restore the database with Binlog, on the other hand to prepare for the main. The original main library configuration parameters are as follows: # VI My.cnfserver-id = 1 #id要唯一log-bin = Mysql-bin #开启binlog日志auto-increment-increment= 1 # After MySQL5.5 in Ubuntu system, the default is 1auto-increment-offset = 1slave-skip-errors =all #跳过主从复制出现的错误1. Main Library Create sync account mysql> grant all on*.* to ' sync ' @ ' 192.168.18.% ' identified by ' sync '; 2. Configuration from library mysql# vi my.cnfserver-id = 3 #这个设置3log-bin = Mysql-bin #开启binlog日志auto-increment-increment= 1 # These two parameters are MySQL5.5 in the Ubuntu system after the default is 1auto-increment-offset = 1slave-skip-errors =all #跳过主从复制出现的错误3. Backup Main 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 state at the beginning of the export, and start the transaction with a consistent snapshot, then unlock tables; And Lock-tables is locked a table can not write operations until the dump is complete. --master-data: Default equals 1, writes dump start (change master to) Binlog point and POS value to the result, equals 2 is writes changemaster to the result and comments. 4. Copy the backup library to the From library # SCP Weibo.sqlroot@192.168.18.214:/home/root5. Create TEST_TB table in main library, simulate database new data, Weibo.sql is not mysql> create TABLETEST_TB (ID inT,name varchar (30)); 6. Import Backup library from library # mysql-uroot-p123-e ' CREATE database Weibo; ' # Mysql-uroot-p123weibo < Weibo.sql7. In backup file Weibo.sql view Binlog and POS values # head-25 weibo.sql--change MASTER tomaster_log_file= ' mysql-bin.000001 ', master_log_pos= 107; #大概22行8. Sync from 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 Slavestatus\g; ERROR 2006 (HY000): MySQLServer has gone awayno connection. Trying Toreconnect ... Connection id:90current Database: ***none ******************************1. Row *************************** slave_io_state:waiting formaster to send event master_host:192.168.18.212 Master_User: Sync master_port:3306 connect_retry:60 master_log_file:mysql-bin.000001 read_master_log_pos:358 Relay_Log_File: mysqld-relay-bin.000003 relay_log_pos:504 relay_master_log_file:mysql-bin.000001 Slave_io_running:yes Slave_sql_running:yes ... 9. View the tables in the Weibo library from the library to see that both the IO and SQL threads are yes, indicating that the master-slave configuration was successful. Mysql> Show tables;+---------------------------+| Tables_in_weibo |+---------------------------+| TEST_TB | found that the TEST_TB table created by the simulation has been synced! Second, Xtrabackup Way (recommended) On the basis of the above configuration to do experiments, first removed from the library configuration:mysql> stopslave; #停止同步mysql > Resetslave; #清除从连接信息mysql > Show slavestatus\g; #再查看从状态, you can see that both IO and SQL threads are nomysql> drop Databaseweibo; #删除weibo库此时, from the library now and the newly installed, continue to move forward! 1. The main library uses xtrabackup backup # Innobackupex--user=root--password=123./Generate a time-named backup directory: 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 Rootroo t 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 Jul 1 16:49 xtrabackup_binlog_info-rw-r-----1 rootroot 1 16:49 xtrabackup_checkpoi nts-rw-r--r--1 rootroot 563 Jul 1 16:49 xtrabackup_info-rw-r-----1 rootroot 2560 Jul 1 16:49 xtrabackup_logfile2. Copy the backup directory to the # scp-r2015-07-01_16-49-43 ROOT@192.168.18.214:/HOME/ROOT3 from the library. Stop the MySQL service from the library, delete the DataDir directory, and rename the backup directory to DataDirCatalog # 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/mysqlstart# ps-ef |grep mysql #查看已经正常启动mysql 8832 1 0 16:55? 00:00:00/usr/sbin/mysqld4. Create a TEST_TB2 table in the main library, add data to the simulation database mysql> create TABLETEST_TB2 (ID int,name varchar (30)), 5. Get the Xtrabackup_info file from the backup directory to the Binlog and POS locations # Cat/var/lib/mysql/xtrabackup_info UUID = 201af9db-1fce-11e5-96b0-525400e4239dname = Tool_name = Innobackupextool_command =--user=root--password= .... /tool_version =1.5.1-xtrabackupibbackup_version =xtrabackup version 2.2.11 based on MySQL server 5.6.24 Linux (x86_64) (re Visionid:) server_version =5.5.43-0ubuntu0.12.04.1-logstart_time = 2015-07-0116:49:43end_time = 2015-07-0116:49:46lock_time = 1binlog_pos = FileName ' mysql-bin.000001 ', position 429 #这个位置innodb_from_lsn = 0innodb_to_ LSN = 1598188partial = Nincremental = Nformat = Filecompact = ncompressed = N6. From library settings sync 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 slavestatus\g;***************************1. Row *************************** slave_io_state:waiting formaster to send event master_host:192.168.18.212 Master_User: Sync master_port:3306 connect_retry:60 master_log_file:mysql-bin.000001 read_master_log_pos:539 Relay_Log_File: mysqld-relay-bin.000002 relay_log_pos:363 relay_master_log_file:mysql-bin.000001 Slave_io_running:yes Slave_SQL_ Running:yes ... 7. View the tables in the Weibo library from the library to see that both the IO and SQL threads are yes, indicating that the master-slave configuration was successful. Mysql> Show tables;+---------------------------+| Tables_in_weibo |+---------------------------+| TEST_TB | | TEST_TB2 | found that the TEST_TB2 table just created by the simulation has been synchronized. Free pick up brother even it education original Linux OPS engineer video/Detailed Linux tutorials, details Inquiry official website customer service: http://www.lampbrother.net/linux/learn PHP, Linux, HTML5, UI, Android and other video tutorials (Courseware + notes + video)! Contact Q2430675018 to participate in the event to receive the brother even original video tutorial CD collection: http://www.lampbrother.net/newcd.html

