Non-stop application services, online creation or redo of MySQL master-slave replication cases, including general mode and Gtid mode

MySQL master and slave, most companies are useful, gtid development to now is more and more people use, stop application services to the master from, slightly low, and now popular online do, do not affect the business, more practical is it, not wordy, now to see the case.

First of all, the case is divided into two schemes, the realization of the meaning is the same, one is the mysqldump way, one is the xtrabackup way, depending on the actual situation, because some businesses may not be able to use Xtrabackup.

first say the mysqldump way ,

Because MySQL comes with, do not need to do something, more convenient and easy to use, but need to emphasize, the amount of data is too large, mysqldump is slightly insufficient, export import time is very expensive, you weigh.

First step, configuration and authorization

If there is no Gtid, the main library does not need to do something, the master-slave attention Server-id can be, this pit is easy to fall into, of course, Binlog is must open, master and slave on this to copy, not open is useless, Binlog_format recommended with Row, Transaction_isolation recommended is Repeatable-read, if you want to use Gtid, it is necessary to open the following two parameters,

#GTID模式, 5.6 new features, new replication mode, need to open, binlog to change to row

Gtid_mode = On

#强制GTID的一致性, which is used in conjunction with the above parameters, but is only allowed to guarantee transaction security when turned on, and can be executed by the SQL statements that are logged, like the CREATE TABLE ... select and create temporarytable statements, and SQL statements or transactions that update both the transaction table and the non-transactional table are not allowed to execute.

Enforce_gtid_consistency = 1

To write to the configuration file, reboot takes effect, because cannot be changed online,

mysql> set gtid_mode = 1;

ERROR 1238 (HY000): Variable ' Gtid_mode ' is a read only Variable

So for some large libraries to be careful to change the use of Gtid(restart failure that tragedy), must use, the main library to find a month of black Wind High, the dead of night when set, from the library there is no so-called.

For a parameter to be noted from the library,

#用来配置从服务器的更新是否写入二进制日志, if there is a hierarchy from the library must be open, not recommended to close, can effectively improve performance

Log_slave_updates

This look at the need to open, there are individual from the library will be mounted from the library to mount, if not open this parameter, then do not, if not to do, not open also nothing, but increase performance.

Configuration changes, I also need authorization, the authorization refers to the main library to read and write Binlog permissions from the library.

Grant Replication Slave on * * to ' rep ' @ '% ' identified by ' password ';

Because I am greedy, direct use of the root user, the following please forgive me.

The second step, data export and import and initialization from the library,

Export and import, in fact, very simple, if the library is too large, take a little more time, as we say, with the mysqldump, the specific parameters I will not say, interested in going to check, directly look at the command is good.

mysqldump-uroot-p123123--opt--default-character-set=utf8--triggers-r--hex-blob--single-transaction-- No-autocommit--master-data=2-a >test2.sql

In this way, we get the full library backup file Test2.sql,

For there is no need to do a full backup of the problem, to see the actual situation, if you are doing mha,pxc, such as the cluster, you must keep the master-slave data is exactly the same, not just the business library, the system library also needs. If you're not going to use it from the beginning, it's really just the business library. There are other things to ignore that do not need to be copied to the library from the parameters, this according to the actual situation you need to do, here is no longer elaborate.

What you need to emphasize is why you can build and re-start online, because of this parameter,

--master-data=2

There will be something special about this parameter later on.

Back to the point, backup done, that is to copy from the library to restore, how to transfer the past, here is not elaborate, what scp,rsync,http,ftp what, you like it, and I, with the SCP

Scp-o Stricthostkeychecking=no [Email protected]:/data/ttt/test2.sql/data/ttt

Commands are for reference only, so you can change them if you like.

Then, I said to initialize MySQL, although now is the test, but generally speaking, the establishment and re-master from, it must be because he is a blank machine, or the main failure, need to redo, the reason I do not say, anyway, similar to Pt-osc tool is useless, it is necessary to redo the posture, As for how to install MySQL I will not say more.

Simply tell me how to initialize it.

Service Mysqld Stop

rm-rf/data/mysql/data*

/usr/local/mysql/scripts/mysql_install_db--defaults-file=/usr/local/mysql/my.cnf--basedir=/usr/local/mysql/-- Datadir=/data/mysql/data--user=mysql >/dev/null 2>&1

Service mysqld Start

/usr/local/mysql/bin/mysqladmin-u root password ' 123123 '

A few simple commands to complete the initialization, and then prepare to import, why say preparation, of course, because there are some preparations, security related to the need to do it, some special settings are also, here to say is Gtid mode, before importing to reset the next Binlog

Reset Master;

The reason is that the import of an instance using Gtid mode requires that the Gtid record of the target instance be empty, otherwise it will be an error, similar to this:

[Email protected] ttt]# mysql-uroot-p123123 < Test2.sql

Warning:using a password on the command line interface can is insecure.

ERROR 1840 (HY000) at line: @ @GLOBAL. Gtid_purged can only is set when @ @GLOBAL. Gtid_executed is empty.

Tip gtid_executed is non-empty and cannot be imported.

Reset the Binlog that's all right, re-execute.

Mysql-uroot-p123123-e ' Reset Master '

mysql-uroot-p123123 < Test2.sql

Well, after the import is done, we check that the general mode is directly

show databases;

Gtid mode and look at this.

Show variables like "%gtid%";

gtid_purged | 16fdabc7-30f9-11e6-9234-0800273e5680:1-3216

You can see this.

Step three, configure Master-slave synchronization

Before I say how to configure, I first say why can I use the service online to make the decision from the reason, remember I said that parameter?, yes, it is--master-data=2, meaning record backup current binlog information.

A lot of people used to be from the main library show master status to view the Binlog location, and then change the master to .... So the application service must be stopped, or the Binlog position has changed, in the middle of the decision from between those statements can not be executed, resulting in data is not synchronized.

After adding the--master-data=2, test2.sql this file will record the current position of binlog, so that we do not have to ignore the decision from the middle of the data, just the Binlog location directly to the SQL file in the Binlog location can be, The data is then synced to its newest location.

Take a look at my test2.sql's binlog position, on the first 30 lines of the file, if you think the file is too large to open, more or head-30 can, do not vim, just look at the keyword

SET @ @GLOBAL. Gtid_purged= ' 16fdabc7-30f9-11e6-9234-0800273e5680:1-3216 ';

--

--Position to start replication or point-in-time recovery from

--

--Change MASTER to master_log_file= ' mysql-bin.000019 ', master_log_pos=1856541;

See, the current binlog position is mysql-bin.000019,position position is 1856541,gtid position is 16fdabc7-30f9-11e6-9234-0800273e5680:1-3216.

Knowing this information, then we can do it.

General mode direct change master to on it.

Change Master to

Master_host= ' 10.0.2.6 ',

Master_user= ' Repl ',

Master_password= ' * * * * *,

master_port=3306,

Master_log_file= ' mysql-bin.000019 ',

master_log_pos=1856541;

Gtid mode, you need to do one more step, that is, execute that statement,

SET @ @GLOBAL. Gtid_purged= ' 16fdabc7-30f9-11e6-9234-0800273e5680:1-3216 ';

This means skipping transactions that have been previously replicated because Gtid records transactions starting with 1, only adding or changing the UUID, and not resetting the counter, so manually set the transaction to skip the previous execution (results already in the backup). Then change master to a bit,

Change Master to

Master_host= ' 10.0.2.6 ',

Master_user= ' Root ',

Master_password= ' 123123 ',

master_port=3306,

Master_auto_position = 1;

In this step gtid a lot simpler, this is why more and more people use the reason, have no regard to where the POS ran, he will find himself.

Then start copying from the library

Start slave;

Last check.

Show Slave Status\g

master_log_file:mysql-bin.000019

read_master_log_pos:4980238

relay_log_file:pingtest1-relay-bin.000002

relay_log_pos:3124105

relay_master_log_file:mysql-bin.000019

Slave_io_running:yes

Slave_sql_running:yes

.

.

.

exec_master_log_pos:4980238

.

.

.

retrieved_gtid_set:16fdabc7-30f9-11e6-9234-0800273e5680:3217-4885

executed_gtid_set:16fdabc7-30f9-11e6-9234-0800273e5680:1-4885

Good, normal, because I have less data, it is easy to catch up, see the difference, but you can be very sure and you say, my main library did not stop the application services, and then from the library is done,

and see how the threads above the main library

Show Processlist;

9172 | Root | 10.0.2.5:57506 | NULL |   Binlog Dump GTID | 75 | Master has sent all binlog to slave; Waiting for Binlog to be updated

Thread is also in, you can make sure that the entire copy is normal.

Next introduce the Xtrabackup way

As I said at the beginning, some libraries are large, dozens of g hundreds of g what is not very surprising, if the use of mysqldump is not scientific, export import time is very expensive, this time must rely on Xtrabackup This physical copy of the Backup and Restore tool, speed up the speed, on the other hand, From the results of the backup principle, the results of mysqldump backup is the data result of the backup start time, and the result of Xtrabackup backup is the data result of the end time of the backup, it belongs to the relatively new data, for the operation of the library with Xtrabackup to do is also obvious advantage .

The first step of configuration and authorization is not much to say, and the same on the line, and there is no difference.

The second goal is consistent with the above, only using the software, and using xtrabackup recovery does not require the initialization of MySQL.

As to how to install xtrabackup not much to say, you can see me another article, how to install xtrabackup2.3.4 and introduce how to use xtrabackup, command I simply paste it out, and then say the use even.

First in the main library back up the whole library (want to only back up a specific library, please do it yourself, this is not extended)

Innobackupex--defaults-file= "/usr/local/mysql/my.cnf"--user=root--password= ' 123123 '--stream=tar "/DATA/TTT" 2 >/data/ttt/backup.log | gzip >/data/ttt/test2.tgz

It means to copy the entire library as indicated in the/USR/LOCAL/MYSQL/MY.CNF configuration file and compress it into a file by gzip.

Copy to Slave library machine

Scp-o stricthostkeychecking=no [email protected]:/data/ttt/test2.tgz./

Then copy to a folder to extract

mkdir Test2back

MV Test2.tgz test2back/

CD test2back/

Tar XF test2.tgz

Start restoring Backup Now

Turn off MySQL service

/etc/init.d/mysqld stop

Delete the old data file first

rm-rf/data/mysql/data*

and Apply-log first.

Innobackupex--defaults-file= '/usr/local/mysql/my.cnf '--user=root--password= ' 123123 '--apply-log/data/ttt/ test2back/

and start recovering.

Innobackupex--defaults-file= '/usr/local/mysql/my.cnf '--user=root--password= ' 123123 '--copy-back/data/ttt/ test2back/

Take a look after the restoration, finish the finishing work

Ll/data/mysql/data

Chown-r Mysql.mysql data/

Start the MySQL service

/etc/init.d/mysqld start

Try logging in, OK is done

Mysql-uroot-p123123

Relatively speaking, the speed is a bit faster, the operation is still simple, is to install a software more.

Step three, configure Master-slave synchronization

In fact, and the above with mysqldump thinking is the same, because Xtrabackup backup when the completion of a file will be generated Xtrabackup_binlog_info, which recorded the Binlog location and Gtid values, it can also be used to keep the service to master from, and using Xtrabackup data is relatively new.

Let's open this file and take a look.

Vim Xtrabackup_binlog_info

mysql-bin.000020 8454162 16fdabc7-30f9-11e6-9234-0800273e5680:1-22037

Very intuitive, on these three values, if you are still interested to note, in fact, Xtrabackup_info also have records.

As I said in the second step above, after the backup restore, the library can be used, set the method is not different from the above mysqldump, if it is non-gtid, the direct change of master to can be used,

Change Master to

Master_host= ' 10.0.2.6 ',

Master_user= ' Repl ',

Master_password= ' * * * * *,

master_port=3306,

Master_log_file= ' mysql-bin.000020 ',

master_log_pos=8454162;

If it's Gtid mode, set it up and skip the previously executed transaction.

SET @ @GLOBAL. Gtid_purged= ' 16fdabc7-30f9-11e6-9234-0800273e5680:1-22037 ';

And look at the state.

Mysql> Show variables like '%gtid% ';

gtid_purged | 16fdabc7-30f9-11e6-9234-0800273e5680:1-22037 |

Then perform change master to to complete

Change Master to

Master_host= ' 10.0.2.6 ',

Master_user= ' Root ',

Master_password= ' 123123 ',

master_port=3306,

Master_auto_position = 1;

Then start copying from the library

Start slave;

Last check.

Show Slave Status\g

master_log_file:mysql-bin.000020

read_master_log_pos:4980238

relay_log_file:pingtest1-relay-bin.000002

relay_log_pos:17522575

relay_master_log_file:mysql-bin.000020

Slave_io_running:yes

Slave_sql_running:yes

.

.

.

exec_master_log_pos:17522575

.

.

.

retrieved_gtid_set:16fdabc7-30f9-11e6-9234-0800273e5680:22038-26950

executed_gtid_set:16fdabc7-30f9-11e6-9234-0800273e5680:1-26950

Still too fast, see no differences, but can be sure that the replication is complete, master-slave architecture completed, the main library is still not stopped.

and see how the threads above the main library

Show Processlist;

32718 | Root | 10.0.2.5:54224 | NULL |   Binlog Dump GTID | 162 | Master has sent all binlog to slave; Waiting for Binlog to be updated

Thread is also in, you can make sure that the entire copy is normal.

Non-stop application services, online creation or redo of MySQL master-slave replication cases, including general mode and Gtid mode