Ukrainian Bantu system MySQL master-slave backup

A. Prepare

System: ubuntu 14.04.2 LTS

Mysql:server version 5.5.43

two hosts can communicate with one another: 192.168.1.11 Master

192.168.1.12 slave

two. Steps

Master section:

1. Create a backup account: slave Password: slave

Grant replication Slave,reload,super,file,select,replication on * * to [email protected] ' 192.168.1.12 ' identi                             fied by ' slave '; If only backup, you can not write Reloads,super,select, Replicatio. The above settings enable manual operation of the master database and data tables

Flush privileges;

2. Log in from the machine to verify your account:

Mysql–u slave–h 192.168.1.11–p

3. Create a database to be backed up

Create database test; can also not be created, directly the database in the system to do backup

Show databases; Querying the database created

4. Export the database to be backed up

Flush tables with read lock; Lock Database

Mysqldump–u root–p Test >/home/wyh/testbak.sql

5. Log in from the Machine Import database ( Root account) to slave (do copy work beforehand)

Create database test; Creating a database with the same name on the slave machine

mysqldump–u root–p Password Test

6. To the host Master unlock, modify the configuration file

Unlock tables;

Vi/etc/mysql/my.cnf

in the [Mysqld] section Add modify the following

Comment out bind-address = 127.0.0.1

Server-id=1

Log_bin=/var/log/mysql/mysql-bin.log

binlog-do-db=test// multiple lines can be added, one row represents a database

Binlog-ignore-db=mysql// multiple lines can be added

Save exit, restart service

Sudo Service mysqld Restart

7. View status information for the host database

Show Master status;

Remember position with the Mysql-bin the numeric value

Configuring the Slave machine

1. To modify a configuration file from a Slave database

vi/etc/mysql/my.cnf in the [Mysqld] section to add the following content

Comment out bind-address=127.0.0.1

server-id=2

Log-bin=/var/log/mysql/mysql-bin.log

replicate-do-db=test// multiple lines can be added

Replicate-ignore-db=mysql// multiple lines can be added

Save exit, restart Service

2. Enter the database and stop slave process, configuring synchronization information

Mysql–u root–p

Stop slave;

Change Master to master_host= ' 192.168.1.11 ', master_user= ' slave ', master_password= ' slave ', master_log_file= ' Mysql-bin.000003 ', master_log_pos=1002;

Start slave;

Show slave status \g;

If it appears

Slave_io_running:yes

Slave_sql_running:yes

Indicates a successful master-slave configuration.

This article is from the "scraps of Knowledge" blog, be sure to keep this source http://10099932.blog.51cto.com/10089932/1655907

Ukrainian Bantu system MySQL master-slave backup