mysql replication from each other based installation and configuration, and data synchronization

2011-02-10  来源:本站原创  分类:Database  人气:81 

One, mysql replication is, what to use

MySQL replication provides a database replication feature enables multiple real-time database synchronization, specifically if the following diagram:

mysql replication from each other based installation and configuration, and data synchronization

mysql replication data synchronization

Why do we use mysql replication, the day when your site independent IP traffic can reach 100W, 1000W total flow can achieve when a single server, simply can not meet existing needs, 100W, 1000W is an example. Although you do a lot of technology above measures, such as memory cache (eg memcache), file cache, ah, be sub-scale for large data tables ah, etc., or websites, or very slow (this may have many causes), Here is an example to MYSQL.

Two, mysql replication of the installation and configuration

mysql 3.23.15 after install mysql when it comes with mysql replication. I did four years ago, the development of that time, I contacted mysql4, if you still have to use mysql3, and then I said silent.

1) Install mysql

Next mysql, installation look into the table and content. I will not say in detail here.

2) modify the my.ini in

a, in to modify the mysql configuration file my.ini, master is a windows system

auto_increment_offset= 2
log-bin-index = d:/wamp/mysql/master-log-bin.index
log-error = d:/wamp/mysql/master-error.log
relay-log = d:/wamp/mysql/slave-relay.log
relay-log-info-file = d:/wamp/mysql/
relay-log-index = d:/wamp/mysql/slave-relay-log.index

b, followed by changes in the region still, configured to slave to the machine



server-id = 1 that is native to the number 1, in general, is the master of meaning.

binlog-do-db = test1 expressed the need to back up the database is test the database,

replicate-do-db = test1 said synchronization test database;

If you need to back up multiple databases, you should write more lines, as follows:

binlog-do-db = test1

binlog-do-db = test2

binlog-do-db = test3

log-bin, said open binlog, this option can only be opened by the I / O Slave write the relay-log, but also can be replication of the premise;

auto_increment_increment define the next steps AUTO_INCREMENT

auto_increment_offset = 1 defines the starting point for AUTO_INCREMENT value

These two parameters are used to avoid multi-master case, simultaneous access to multiple master AUTO_INCREMENT field type of conflict. Several other field information for the log file configuration

set-variable = max_connections = 500 set the maximum number of connections to the database 500

master-host = said that when the machine do the slave master is;

master-user = xtajmd here that the master ( on the opening of a privileged user, it can connect to the master and slave replication; proposed two hosts authorized users and passwords are identical.

master-password = xtajmd that authorized user's password;

master-port = 3306, said master ( port on the MySQL service Listen3306;

master-connect-retry = 60 synchronization time interval;

log-slave-updates: updates from the master read operation is logged to the slave the binary log

3) modify the my.cnf on the

vi my.cnf


# The MySQL server

Add the following section in which mysqld

server-id=2                                     # Native serial number, do not and master are the same
log-bin-index = /usr/local/mysql/master-log-bin.index
log-error = /usr/local/mysql/master-error.log
relay-log = /usr/local/mysql/slave-relay.log
relay-log-info-file = /usr/local/mysql/
relay-log-index = /usr/local/mysql/slave-relay-log.index

4), prepare two different database

Above from one machine to export a mysql database, what database are OK, and then import it into another machine's data.

Please refer to:

5) the establishment of accounts, assign permissions

In and on the mysql log, respectively, operate as follows:



In and, respectively tested by the user can add a normal landing another server.

In on: mysql-u xtajmd-p-h

mysql-u xtajmd-p-h
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 11
Server version: 5.0.27-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\ h' for help. Type '\ c' to clear the buffer.


In on: mysql-u xtajmd-p-h

mysql replication from each other based installation and configuration, and data synchronization


If the two sets of data synchronization between linux system, then make sure that both ports 3306 iptables on the server has been opened to ensure communication is normal.

If the windows and linux data synchronization between systems, then make sure the linux server on port 3306 iptables has been opened, windows following firewall, antivirus software is not allowed to connect to the outside world through the local database 3306

6) The two servers are restarted mysql, check the master, slave state, and attention problems

On the

mysql> show master status;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| Master-bin.000072 | 98 | test1 | |
1 row in set (0.00 sec)

On the

mysql> show slave status \ G;
*************************** 1. Row ******************** *******
Slave_IO_State: Waiting for master to send event
Master_User: xtajmd
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000072
Read_Master_Log_Pos: 98
Relay_Log_File: slave-relay.000330
Relay_Log_Pos: 244
Relay_Master_Log_File: master-bin.000072
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1

Note: Here Slave_IO_State: behind may not have any parameters, or Waitting to connect

We must ensure, master inside the File and Master_Log_File content is the same, position and Read_Log_File_Pos to be the same.

In turn, have the same. If you do not, like how to do it? As

Troubleshooting steps:
Restart the master database:
mysql> show master status;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| Mysql-bin.000072 | 98 | test1 | |
mysql> slave stop;
mysql> change master to Master_Log_File = 'mysql-bin.000072', Master_Log_Pos = 98;
mysql> slave start;
mysql> show slave status \ G

Here basically be configured, now to test it, in the 91's database test1 inside, add tables to try to see if there is no 5 above. Haha.