Build a slave database based on the existing MySQL master database _ MySQL

The process of building a slave database based on the existing MySQL master database bitsCN.com

Build a slave database based on the existing MySQL master database

[Note ]:

Host 192.168.250.20

Backup machine 192.168.250.40

Master-slave replication

1. MySQL 5.1.53-log is available on the host.

2. if there is no mysql database on the backup machine, install the MySQL5.1.53-log software on the backup machine. for details, refer to the svn document "system/mysql/document/IT_SYS_MySQL_Installation.docx ".

3. confirm again that the master server is consistent with the slave server's MySQL version.

  Showvariables like 'version';

4. set up a connection account on the master server and grant the replication slave permission.

MySQL> grant replication slave, super,reload  on *.* to 'rel'@'192.168.250.40 identified by'slavepwd1229';

5. make sure that the [mysqld] section of the my. cnf file on the master server contains a log-bin option. As follows:

   [mysqld]   ………    log-bin=mysql-bin    server-id=1binlog-ignore-db=mysql binlog-ignore-db=information_schema

6. Make sure to add the following lines to the my. cnf file on the slave server:

[Mysqld] log-bin = mysql-bin server-id = 2log_slave_updates = 1read_only = 1 # this parameter indicates that only supserrelay-log = relay-binrelay-log-index = relay-binreplicate- ignore-db = mysqlreplicate-ignore-db = information_schemalog_bin_trust_function_creators = 1

7. restart the master server (if the master service is configured, the slave server can be restarted ).

N The Master server has been running for a long time, and the binlog does not start from 0001.

8. view the master status on the master server

mysql> show master status;+------------------+-----------+--------------+------------------+| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+-----------+--------------+------------------+| mysql-bin.000075 | 875653787 |              |                  |+------------------+-----------+--------------+------------------+1 row in set (0.00 sec)

9 start the slave server and set the copy breakpoint

First copy the data file from 250.20 to 250.40, then chown data permission, and finally refresh the table

Mysql> flush tables;

Import all stored procedures and stored functions from 102:

/usr/local/mysql/bin/mysqldump -h192.168.250.20-uroot -pguNNhtqhjUnfky6ahyVh -ntd -R csf_maec > /tmp/cm20101230.sql;/usr/local/mysql/bin/mysqldump -hlocalhost-uroot -pguNNhtqhjUnfky6ahyVh -ntd -R csf > /tmp/csf20101230.sql;/usr/local/mysql/bin/mysqldump -hlocalhost-uroot -pguNNhtqhjUnfky6ahyVh -ntd -R csf_pub > /tmp/csf_pub20101230.sql;/usr/local/mysql/bin/mysqldump -hlocalhost-uroot -pguNNhtqhjUnfky6ahyVh -ntd -R reuters_interface >/tmp/reuers_inter20101230.sql;   Mysql> change master tomaster_user='rel',master_password=' slavepd1012301151' ,master_host='192.168.250.20’,master_log_file='mysql-bin.000075',master_log_pos=875653787;mysql> start slave;mysql> show slave status /G;

10 if the replication status is Slave_IO_Running and Slave_ SQL _Running is Yes, the replication configuration is correct, as shown below:

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

OK. the insert, delete, update, drop, and truncate tests are successful.

Accident 1:

The host is down and the slave is normal: after the host is restarted, the slave can still perform replication and synchronization normally.

Accident 2:

The host is not stopped, and the slave machine is stopped: after the slave machine is restarted, the manual copy function is enabled for the slave machine as follows:

Mysql> start slave;

Accident 3:

Host shutdown and slave shutdown: (1) enable slave first, start mysql, and enter mysqlMysql> stop slave; Mysql> show slave status/G; write down the last pos and binlog values for the mysql-bin.000079, 875653787 (2) Re-specify the master target: Mysql> change master tomaster_user = 'Rel ', master_password = 'slavepd1012301151', master_host = '100. 168.250.20, master_log_file = 'MySQL-bin.000079 ', master_log_pos = 875653787; (3) Start master (4) to enter slave and start the replication function. Run start slave; Mysql> start slave;

BitsCN.com