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