Mysql master-slave backup and SQL statement backup, mysql master-slave SQL statement

Mysql master-slave backup and SQL statement backup, mysql master-slave SQL statement

The master-slave configuration of the MySQL server is a very simple task. Instead of starting from scratch, it is always on the mysql server that has been installed by others. This will involve the mysql version, startup File and other issues.

Http://www.cnblogs.com/roucheng/p/phpmysql.html

But it doesn't matter. Let's clarify two points first.

1. mysql configuration file my. cnf location

2. How to start and stop mysql and find the Startup File

Suppose there are two machines and mysql has been installed (try to use the same version, and the two machines have the same network, you can ping)

A friend said, "The slave server cannot be lower than the master server version." However, I am lower than the master server version and there is no problem.

Host A: 192.168.1.100

Slave B: 192.168.1.101

Multiple slave servers

1. log on to host A first.

Mysql> grant replication slave on *. * TO 'backup '@ '192. 168.1.101 'identified BY '123 ';

Grant permissions to the slave machine. If multiple slave machines exist, the slave machine is executed multiple times.

2. Open my. cnf of host A and enter

Server-id = 1 # host id, integer
Log_bin =/var/log/mysql/mysql-bin.log # Make sure this file is writable
Read-only = 0 # host, both read and write are allowed
Binlog-do-db = test # You need to back up data and write multiple rows.
Binlog-ignore-db = mysql # databases that do not need to be backed up, multiple write lines

3. Open my. cnf of slave machine B and enter

Server-id = 2
Log_bin =/var/log/mysql/mysql-bin.log
Master-host = 192.168.1.100
Master-user = backup
Master-pass = 123456
Master-port = 3306
Master-connect-retry = 60 # Time Difference (in seconds) when the master server is disconnected from the server)
Replicate-do-db = test # only copy a database
Replicate-ignore-db = mysql # do not copy a database

4. Synchronize Databases

There are multiple ways to export the data test of host A to test. SQL first.

Then, create the database test from machine B. mysql imports test. SQL to the database test.

5. Restart mysql of host A and mysql of slave B.

6. Verification

In host A, mysql> show master statusG;

In slave machine B, mysql> show slave statusG;

You can see the following content:

File: mysql-bin.000001
Position: 1374
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
You can perform INSERT, UPDATE, and DELETE operations in host A to check whether the operation has been modified in host B.

Http://www.cnblogs.com/roucheng/p/mysql.html

 

Using Binary backup

Add the following to my. ini:

[Mysqld]

Log-bin = shenghua_binlog

 

Show master status; // view the bin name // master server

 

 

Show binlog events; // view the data inserted by binlog

 

This is binary data.

Mysqlbinlog D: wampinmysqlmysql5.0.51bdata _ binglog.000001> D: hhy. SQL

 

Master/Slave

Server-id = 1

Log-bin = shenghua_binglog

# Databases to be backed up

Binlog-do-db = hhy

# Databases that do not need to be backed up

# Binlog-ignore-db = **

Set From Server

Server-id = 2 # (set the id number in sequence When configuring multiple slave servers)
Master-host = 192.168.0.1 # master server
Master-user = shenghua # user name authorized by the master server
Master-password = hhy # password authorized by the master server
Master-port = 3306 # master server port

Replicate-do-db = hhy # Name of the database to be backed up. If multiple databases are backed up, set this option again.

Restart Master/Slave

Slave start;

Show slave status;

The values in the Slave_IO_Running and Slave_ SQL _Running columns are "Yes", indicating that the Slave I/O and SQL threads are running properly.

Http://www.cnblogs.com/roucheng/p/mysqlfqb.html