A method to realize the synchronization of two MySQL database data _linux

1. Installation Configuration

Two servers, respectively installed MySQL, are installed in the/usr/local/mysql directory (installation steps omitted, please refer to the relevant documents), The IP of two servers is 192.168.0.1 and 192.168.0.2 respectively, we take 192.168.0.1 as master database, 192.168.0.2 as slave server, we adopt one-way synchronous way, that is master data is master's data, then slave main Move to master where to synchronize the data back.

As with the configuration of the two servers, we copy the key configuration files, the default profile is in the/usr/local/mysql/share/mysql directory, there are my-large.cnf, MY-MEDIUM.CNF, My-small.cnf and so on a few Wen Jia, we just test, use my-medium.cnf on the line. After the MySQL installation, the default configuration file is specified in the database storage directory, we use 4.1.X, so the configuration file should be in the/usr/local/mysql/var directory, so the configuration file copy the past:

CP/USR/LOCAL/MYSQL/SHARE/MYSQL/MY-MEDIUM.CNF/USR/LOCAL/MYSQL/VAR/MY.CNF two servers to do the same copy configuration file operation.

2. Configure Master server

We're going to configure the 192.168.0.1 as the primary MySQL server (master), so we have to consider that we need to sync that database and use that user to sync, and we're going to use the root user to sync for simplicity, and just synchronize the database ABC.

To open a configuration file:

VI/USR/LOCAL/MYSQL/VAR/MY.CNF Find information:

# required Unique ID between 1 and 2^32-1phpchina Open source community Portal d%wm/x-y3u_$zs# defaults to 1 if master-host are not setg~8 fpv9w37313# but would not function as a master if omitted
ML.? K
? Nzow37313server-id = 1//1 adds two lines to the master,2 for salve:

Sql-bin-update-same//Synchronous Form Phpchina Open Source community Portal U$m:}s ' VBINLOG-DO-DB = ABC//sync database restart 192.168.0.1 mysql server:

/usr/local/mysql/bin/mysqladmin Shutdownphpchina Open Source community portal 0w+g!? &p0d U u#mb i/usr/local/mysql/bin/mysqld_safe--user=mysql &3. configuring Slave servers

Our slave server is mainly active to the master server to synchronize data back, we edit the configuration file:

VI/USR/LOCAL/MYSQL/VAR/MY.CNF found the following similar information:

# required Unique ID between 1 and 2^32-13fs8oon lj8o37313# defaults to 1 if master-host are not Setphpchina open source community Portal 9U o7x "TRT
gb$ku# but would not function as a master if OMITTEDCWO @8pmw37313server-id = 1 Change the above Server-id to 2 and add some information:

server-id                    = 2                         //MySQL is the slave server. h wjc9c ' Hu37313master-host              = 192.168.0.1     //master Server ipphpchina  Open Source community Portal}~q    ~q_master-user              = root                   //users connecting to master server phpchina  open Source community Portal *_ b\9n (tmaster-password   =  '                          //the password to connect to the master server Phpchina   Open Source Community Portal 6o&ys;ruemaster-port              = 3306                 //connection Port s@.@ "N &SB k37313master-connect-retry    = 10               //Retry number 4 ']w^0qw; n2j37313replicate-do-db        = abc                   //the database to be synchronized phpchina  Open Source Community portal ', q/c? $hC *llog-slave-updates                               // Restart 192.168.0.2 MySQL server in the form of synchronization: 

/usr/local/mysql/bin/mysqladmin Shutdownphpchina Open Source community portal. wh1]^~!| V$b0\/usr/local/mysql/bin/mysqld_safe--user=mysql &4. Test installation

First look at the slave host log:

Cat/usr/local/mysql/var/xxxxx_err (XXX is the hostname) check to see if the connection is normal.

051031 11:42:40 mysqld Startedphpchina Open Source Community portal w2l0com r[^ b9s-rib051031 11:42:41 innodb:started; log sequence Numbe R 0 43634PHPChina Open source Community Portal uqt7y! Df$c
AK "Bl/usr/local/mysql/libexec/mysqld:ready for connections. Phpchina Open Source community portal; |0g|;    Vhfz7}version: ' 4.1.15-log ' socket: '/tmp/mysql.sock ' port:3306 source Distributionphpchina Open Source Community portal (j9z,kg lx%) i6ow051031 11:42:41 [note] Slave SQL thread initialized, starting replication in log ' a ', Phpchina open source community Portal Q9n-bu8[a Yc laat position 0, relay log './new4-relay-bin.000001 ' Position:4phpchina Open source community Portal J V ' jb\n7s*w051031 11:43:21 [note ] Slave I/O thread:connected to master ' root@192.168.0.1:3306 ',]-{eyn f5vk37313replication started in log ' a ' At position 4 View information in master

/usr/local/mysql/bin/mysql-u root View master status:

Mysql> Show master status; View MySQL process information under master:

Mysql> show processlist; View information on slave:

/usr/local/mysql/bin/mysql-u root view slave Status:

Mysql> show slave status; View MySQL process information under slave:

Mysql> Show Processlist you create a table structure in master's ABC library and insert the data, and then check that slave has synchronized the data to check whether the setting is successful.

Finally, if you are interested, you can study the double click Hot Backup, or a master, multiple slave synchronization implementation.