Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to configure master-slave MySQL in Percona. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Backup step

MyISAM

If all your watches are MyISAM. Just run innobackupex-prepare / directoryWhereBackupIs. Then move the database directory from / directoryWhereBackupIs to the datadir of your slave. Then modify the users and groups of the database file. In the end, just run change master on slave's server and you can do it.

But if the database is an innodb table, you need to add three steps. You need to stop mysql on slave. You need to put ibdata1 in datadir, and then restart mysql. And then running "change master..." And "start slave" command.

1. Install mysql client, server, and XtraBackup in Percona

Rpm-Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.$(uname-I) .rpm

two。 Configure / etc/my.cnf on all machines from master to slave.

Percona sometimes does not generate / etc/my.cnf files in this directory. So you need to copy a configuration file from the installation directory / usr/share/mysql/my-small.cnf to change it to what you want.

Here, we also need to configure to support slave. If you configure master and slave, you need to modify at least three lines in [mysqld].

Log-bin=mysql-bin

Server-id=a unique number

Datadir=/var/lib/mysql

The first two lines are necessary for replication, and the master and slave are realized through the bin log. The third line is for innobackupex.

If you want to record only one of the databases for master-slave synchronization, you can record only one basic binlog. Just add the following configuration.

Binlog-do-db = test # generate binlog only for test's database

Preferably, configure read-only = 1 and skip-slave-start on your slave. If necessary, the master is also configured to read only the first time the master and slave are configured to prevent data from being out of sync in the backup. The backup is complete and then writable is turned on. I don't have to take the test, but just to be on the safe side, I'd better stop for a while. You can change it to read-only online using the following command.

3. On master, run the following command:

Before that, you need to put ssh's key on slave so that you don't need to enter a password when backing up:

Innobackupex-- databases=test-- stream=tar / tmp/-- slave-info | ssh root@slave_ip "tar xfi-C / tmp/mysql"

If successful, you will see the following message:

120111 12:26:28 innobackupex: completed OK!

Tar: Read 3584 bytes from-

Notice the output on the third line, which is the filename and position of the binlog file, like this:

Innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 3287

If you usually back up, just use the following command.

Innobackupex-user=root-password=test-stream / tmp/mysql 2 > / tmp/mysql.log | gzip > / tmp/data/mysql.tar.gz

4. On slave, run the following command:

Innobackupex-apply-log / tmp/mysql

This step is to recover the log and parse it into the database format.

Innobackupex-copy-back / tmp/mysql

This step is only to give the file move to / var/lib/mysql, if this step fails, you can just move it yourself.

If the database we imported is called test, we need to change it to the user group of mysql:

Chown-R mysql:mysql / var/lib/mysql/test

Note that if the database has tables for innodb, you need to stop mysql on slave, copy ibdata1 under / var/lib/mysql/ to datadir, and then restart mysql.

5. On master, you need to create a duplicate account:

Grant replication slave, replication client on *. * to repl@'219.32.22.%' identified by 'password'

FLUSH PRIVILEGES

6. On slave, configure master information:

Change master to master_host='master_ip', master_user='repl', master_password='password', master_log_file=' previous output filename', master_log_pos= of innobackupex backup on master before output position of innobackupex backup on master

Start slave

7. Check after the completion of the configuration to confirm that both the master and slave are working properly.

Basic check, error message will also be displayed here

Show slave statusG

In this one, be sure to show

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

These two behaviors Yes is the normal work.

Displays the status of the master

Show master statusG

Show the work of the synchronization thread

Show processlistG

FQA:

1. If the synchronization is different, consider resetting permissions, and then do the following from the host.

Slave stop

Reset slave

Slave start

If not, consider replacing reset slave; with what you did in step 6 above.

two。 If you prompt "Possible values are xtrabackup_51 (for MySQL 5.0 and 5.1) or xtrabackup (for MySQL 5.1 with InnoDB plugin or Percona Server)"

Innobackupex-apply-log / tmp/mysql-ibbackup=xtrabackup_51

After reading the above, do you have any further understanding of how to configure master-slave MySQL in Percona? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.