Shulou(Shulou.com)05/31 Report--

How to understand Mysql Replication, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Mysql Replication

It is similar to copying a database from one server to another, but it ensures the full synchronization of the two databases in real time by defining the relationship between Master and Slave. This feature began to appear in version 3.23 of Mysql.

Master/Slave mode backup

TestEnv:

Master:Mysql-4.1.12 on Redhat9.0 IP:192.168. 0.217

Slave: Mysql-4.1.12 on Redhat9.0 IP:192.168.10.244

1. Compile, install

1. # tar-zxvf Mysql-4.1.12.tar.gz

2. # cd Mysql-4.1.12

3. # / configure-prefix=/var/eyou/mysql

4. # make

5. # make install

6. # chown-R root / var/eyou/mysql

7. # chown-R mysql/var / eyou/mysql/var

8. # chgrp-R mysql / var/eyou/mysql

9. # scripts/mysql_install_db

10. # cp support-files/my-medium.cnf / etc/my.cnf

2. Set permissions for Master machines, give Slave machines FILE and Replication Slave rights, and package the database structure to be synchronized.

Master# pwd

/ var/eyou/mysql/bin

Master#./mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or g.

Your MySQL connection id is 2 to server version: 4.1.12

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

Mysql > GRANT FILE ON *. * TO rep@192.168.0.244 IDENTIFIED BY 'eyou'

Mysql > GRANT REPLICATION SLAVE ON *. * TO rep@192.168.0.244 IDENTIFIED BY 'eyou'

Give 192.168.10.244, that is, the Slave machine has File permission. This version 4.1.12 seems to have adjusted the permission of replication. It is not enough to give the Slave machine File permission, but also to give it REPLICATION SLAVE permission.

Then package the database to be replicated

Master# cd var

Master# tar czvf reptest.tar.gz reptest

In this way, we get a package file reptest.tar.gz of the reptest database

2 set the my.cnf of the master server Master and start the Mysql service

Master# vi / etc/my.cnf

Add or modify the following in [mysqld]

[mysqld]

Log-bin # turn on the logbin option to write to slave's Igamot O thread

Server-id=1 # means the serial number of the machine is 1, which generally means master.

Sql-bin-update-same

Binlog-do-db= reptest # means to synchronize the reptest database

Then restart the Mysql of the Master master server.

Master# / var/eyou/mysql/bin/mysqladmin-u root-p shutdown

Master# / var/eyou/mysql/bin/safe_mysqld-- user=mysql &

3. Establish Slave database.

I just packaged reptest.tar.gz in Master, and its purpose is to restore to the same database in Slave. First transfer the reptest.tar.gz file of Master to the Slave machine. And then

Slave# tar zxvf reptest.tar.gz-C / var/eyou/mysql/var/

4. Modify the my.cnf of the Slave server

Slave# vi / etc/my.cnf

Add or modify the following in [mysqld]

Master-host=192.168.10.217

Master-user=rep

Master-password=eyou

Master-port=3306

Server-id=2

Master-connect-retry=60

Replicate-do-db=reptest [database to be updated]

Log-slave-updates

5. Delete the master.info in the server database directory

Slave# rm / var/eyou/mysql/var/master.info

6. Restart the slave start of Slave.

Slave# / var/eyou/mysql/bin/mysqladmin-u root-p shutdown

Slave# / var/eyou/mysql/bin/safe_mysqld-- user=mysql &

7. Testing

First check whether the reptest in the two Mysql databases is normal.

Normally, the Mysql in Master and Slave both have the same reptest database, and the data in it is the same.

Then test whether the replication feature is enabled.

Add a piece of data to the reptest database in Master:

Master# / var/eyou/mysql/bin/mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or g.

Your MySQL connection id is 12 to server version: 4.1.12

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

Mysql > use reptest

Database changed

Mysql > INSERT INTO rep_table VALUES ('test1',' 4321, 'Toner, 24)

Query OK, 1 row affected (0.00 sec)

Mysql >

Then look at the reptest database of the Slave machine:

Slave#/var/eyou/mysql/bin/mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or g.

Your MySQL connection id is 12 to server version: 4.1.12

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

Mysql > use reptest

Database changed

Mysql > select * from reptable

+-+

| | id | name | sex | age | |

+-+

| | test1 | 4321 | T | 24 | |

+-+

1 row in set (0.00 sec)

PS:

1Jing Slave machine permissions problem, not only to give the slave machine File permissions, but also to give it REPLICATION SLAVE permissions.

2. After modifying the Slave machine / etc/my.cnf, and before starting the mysql service on the slave machine, remember to delete the master.info

3. When show master status or show slave status is not normal, see what .err says.

4There are two threads in the Replication work of Mysql on Magazine Slave, I get O thread and SQL thread. The function of binlog O is to take its binlog from port master 3306 (after master has been modified, it will write to its own binlog to wait for slave update), and then write to the local relay-log, while SQL thread is to read the local relay-log, and then convert it into statements that Mysql can understand, so synchronization is completed step by step. It is / var/lib/mysql/master.info that determines the thread and / var/lib/mysql/relay-log.info that determines the SQL thread.

Two-way replication mode

1:

Set permissions for Slave machines and give Master machines FILE and Replication Slave rights.

Master#./var/eyou/mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or g.

Your MySQL connection id is 2 to server version: 4.1.12

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

Mysql > GRANT FILE ON *. * TO rep@192.168.0.217 IDENTIFIED BY 'eyou'

Mysql > GRANT REPLICATION SLAVE ON *. * TO rep@192.168.0.217 IDENTIFIED BY 'eyou'

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.