MySQL5.1 version of the master-slave copy Construction

MySQL5.1 version of the master-slave copy Construction

       

"Remarks":

Host 192.168.250.20

Standby Machine 192.168.250.40

Master-slave replication

1 The host already has the mysql5.1.53-log version .

2 There is no MySQL database on the standby, you need to install the mysql5.1.53-log software on the standby, specifically refer to the svn documentation "system\mysql\ Document\it_sys_mysql_installation.docx".

3 Confirm that the master server is consistent with the MySQL version from the server

Showvariables like ' version ';

4 Set up a connection account on the host server and give replication slave permissions.

mysql> grant replication Slave, super,reload on * * to ' rel ' @ ' 192.168.250.40 identified by ' slavepwd1229 ';

5 Ensure that the [mysqld] section of the my.cnf file on the primary server includes a log-bin option. As follows:

[Mysqld]

.........

Log-bin=mysql-bin

Server-id=1

Binlog-ignore-db=mysql
Binlog-ignore-db=information_schema

6 Make sure to add the following line to the my.cnf file from the server:

[Mysqld]

Log-bin=mysql-bin

server-id=2

Log_slave_updates = 1

Read_only=1 # This parameter means only supser

Relay-log=relay-bin

Relay-log-index=relay-bin

Replicate-ignore-db=mysql

Replicate-ignore-db=information_schema

log_bin_trust_function_creators= 1

7 Primary server reboot (can not restart if Master service is configured), restart from server

n The master server has been running for a long time,binlog not starting from 0001 .

8 Viewing the master status on the primary 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 starting from the server, set the replication breakpoint

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

mysql> flush Tables;

Import all stored procedures and stored functions from 102 above:

/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;

If the replication status is slave_io_running,slave_sql_running is Yes, indicating that replication is configured correctly, as follows:

　　　　Slave_io_running:yes

　　　　Slave_sql_running:yes

Ok, the following insert,delete,update,drop,truncate , and so on are tested successfully.

Accident 1:

host shutdown, standby machine normal: After the host restarts, the standby machine can still be copied and synchronized normally.

Accident 2:

host does not stop, standby machine shutdown: After the standby machine restarts, the standby machine to open the manual copy function as follows:

mysql> start slave;

Accident 3:

host shutdown, standby machine shutdown:

(1) first open slave, start mysql, enter MySQL

mysql> stop Slave;

mysql> show slave status \g; Make a note of the last Pos with binlog value of mysql-bin.000079,875653787

(2) Re- Specify the master target:

mysql> Change Master tomaster_user= ' rel ',

Master_password= ' slavepd1012301151 ',

Master_host= ' 192.168.250.20,

Master_log_file= ' mysql-bin.000079 ',

master_log_pos=875653787;

(3) start master

(4) enter slave, start the copy function.

MySQL5.1 version of the master-slave copy Construction