MySQL master-slave hot backup in Linux

A hot backup of MySQL on the primary server is required in the near future, i.e. data is synchronized in real time.

Refer to the article as follows: http://369369.blog.51cto.com/319630/790921/

My environment is as follows: Host a ip:42.62.25.47 from machine B ip:106.3.37.197

Data that requires a hot backup is VBUMNG

1. Prerequisites : The MySQL version of host a must not be higher than the MySQL version of slave b

2. Modify the configuration file for host a

My configuration file directory is/ETC/MY.CNF

Add The following configuration to the [mysqld] Module

[Mysqld]log-bin = Mysql-bin <!--enable binary files-->log-slave-updates = Turebinlog-do-db = vbumng <!--database name to back up-- Binlog-do-db = cdnquality <!--If you want to back up multiple databases-->server-id = 47

Ps:server-id must be configured, default is 1, generally take IP last paragraph

Log-bin the binary log file for the database,

If you want to back up multiple databases on a host, add multiple binlog-do-db

Host a MySQL configuration has been modified so far

3. Host a MySQL restart, create user and authorize

Restart Host a mysql/etc/init.d/mysqld start, enter MySQL

Create a user and authorize slave, which the user needs to use from the machine

GRANT REPLICATION SLAVE on * * to ' resourcemanage ' @ '% ' identified by ' chinacache.gz ';//typically without root account, "%" means that all clients can connect, As long as the account password is correct, here can be replaced by specific client IP, such as 106.3.37.197, enhance security.

View Master Status

Mysql>show Master status; +------------------+----------+--------------+------------------+   | File | Position | binlog_do_db |   binlog_ignore_db | +------------------+----------+--------------+------------------+   |      mysql-bin.000004 |  308 |                  TestDB |   | +------------------+----------+--------------+------------------+ 1 row in Set (0.00 sec)

Note: Do not operate the master server MySQL after performing this step, prevent the change of the primary server state value, the file here is the same as the binary file format established in the configuration file.

4. Configure the slave B configuration file

#vi/etc/my.cnf [mysqld] Log-bin=mysql-bin//[must] enable binary log server-id=226//[must] server unique ID, default is 1, generally take IP most The latter section

Many articles on the Internet are mentioned here, put master-host,master-user , etc., but after the mysql5.0 is not supported, joined the database can not start.

5. Restart slave MySQL, configure the corresponding master information

restart slave b mysql

/etc/init.d/mysql restart

The host TestDB data is fully copied to the slave, be sure to manually back up the past, because the master-slave slave only synchronous start slave

After the execution of the command to delete and change the operation.

Configuring the Slave Machine Slave

Change Master to master_host= ' 42.62.25.47 ', master_user= ' TestDB ', master_password= ' chinacache.gz ', master_log_file= ' Mysql-bin.000004 ', master_log_pos=308;

ps:master_host--Host IP

master_user--databases that need to be backed up

master_password--slave password created by the host

The corresponding log file in the Master_log_file--master status

Master_log_pos--master status in the corresponding position, host here Master_log_pos do not need quotation marks

Open Slave

 mysql>start slave; mysql>show slave status\g ***************************  1. row ***************************                slave_io_state: waiting for master to send event                    master_host: 42.62.25.47     <!--Host ip-->                   Master_User: vbumng            <!--Host DB name-->                   Master_Port: 3306              <!--Host DB Port-->                 connect_retry: 60               master_log_file: mysql-bin.000004   <!- -Host Master log file-->          read_master_log_pos: 40543                relay_log_file:  mysqld-relay-bin.000006                 relay_log_pos: 3489        relay_master_log_ file: mysql-bin.000004               slave_io_running: yes            slave_ sql_running: yes               Replicate_do_db: vbumng                    &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP, ........ ........

PS: If slave_io_running, slave_sql_running all show Yes, then Slave success, you can add data in the host database, the data will be found in the slave in real-time synchronization.

If one is no, you need to review the log.

So far, a simple master one from there is no problem, if it is a host of multiple databases, respectively, backup to different slave, then the host my.cnf configuration of multiple binlog-do-db, from the machine then the flow of the same as above.

This article is from the "Bulajunjun" blog, make sure to keep this source http://5148737.blog.51cto.com/5138737/1570274

MySQL master-slave hot backup in Linux