| |
First. Overview
MySQL offers from later versions 3.23.15 database replication (replication) feature This feature allows you to achieve two databases synchronized, master-slave mode, another function of the backup mode
Second, the environment
Operating System: Linux 2.6.23.1-42.fc8 # SMP (do not install XEN)
Mysql version: 5.0.45-4.fc8
Device environment: PC (or virtual machine) two
Third, the configuration
Database replication feature set are reflected in the configuration file of MySQL, MySQL configuration file (usually my.cnf): Under this environment /etc/my.cnf.
3.1 Set the environment:
IP settings:
A host IP: 10.10.0.119
Mask: 255.255.0.0
B Host IP: 10.10.8.112
Mask: 255.255.0.0
After IP setup is complete, you need to determine the two host-based firewall, it has been closed. You can use the command service iptables status to view the status of the firewall. If the firewall status is still running. Using service iptables stop to disable the firewall. If you want to start off the firewall can disable or customize using the setup command.
Eventually two hosts can ping each other better.
3.2 Configuration A primary (master) B from (slave) mode
3.2.1 A configured as master
1, add a user account simultaneous use:
. GRANT FILE ON * * TO 'backup'@'10.10.8.112' IDENTIFIED BY '1234';
. GRANTREPLICATION SLAVE ON * * TO 'backup'@'10.10.8.112' IDENTIFIED BY '1234';
10.10.8.112 is assigned Slave machine has File permissions, giving the machine has only Slave File permission is not enough, but also to give it REPLICATION SLAVE privilege can.
2, add a database as a synchronized database:
create database test;
3, create a table structure:
create table mytest (username varchar (20), password varchar (20));
4, modify the configuration file:
A modification of the /etc/my.cnf file and add the following in the my.cnf configuration item configuration:
server-id = 1 #Server logo
log-bin
binlog-do-db = test # Specifies the log database
5, restart the database service:
service mysqld restart
See server-id:
show variable like 'server_id';
Example:
mysql> show variables like 'server_id';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server_id | 1 |
+ --------------- + ------- +
1 row in set (0.00 sec)
6, with the show master status / G command to see the log situation.
Normally:
mysql> show master status / G
*************************** 1. row ******************** *******
File: mysqld-bin.000002
Position: 198
Binlog_Do_DB: test, test
Binlog_Ignore_DB:
1 row in set (0.08 sec)
3.2.2 Configuration B is a slave
1, add a database as a synchronized database:
create database test;
2. Create a table structure:
create table mytest (username varchar (20), password varchar (20));
3, modify the configuration file:
Modify /etc/my.cnf file B, add the following configuration in my.cnf configuration item:
server-id = 2
master-host = 10.10. 0.119
master-user = backup # synchronize user accounts
master-password = 1234
master-port = 3306
master-connect-retry = 60 # default retry interval 60 seconds
replicate-do-db = test # tell slave only do backup database update
5, restart the database service:
service mysqld restart
See server-id:
show variables like 'server_id';
Example:
mysql> show variables like 'server_id';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server_id | 2 |
+ --------------- + ------- +
1 row in set (0.00 sec)
6, with the show slave status / G command to see the log situation.
Normally:
mysql> show slave status / G
*************************** 1. row ******************** *******
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.0.119
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 236
Relay_Master_Log_File: mysqld-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test, test
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 236
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.01 sec)
3.2.3 Authentication Configuration
Respectively using insert, delete, update the A host CRUD database; View B's database is consistent with the host A host; if agreed, the configuration was successful.
3.3 pairs of active preparation mode
If the slave set to join the A host B host joins the master set, you can do B-> A synchronization of.
1, A host configuration file mysqld configuration item by adding the following settings:
master-host = 10.10.8.112
master-user = backup
master-password = 1234
replicate-do-db = test
master-connect-retry = 10
2, B in the configuration file in the mysqld configuration item by adding the following settings:
log-bin
binlog-do-db = test
Note: When an error occurs, the thread exits * err log file synchronization, when the correct error to make synchronization work, run the slave start.
Up the A, B machine, you can achieve two-way hot spare.
IV Common Problems and Solutions
1, permission issues Slave machine, not only to give the slave machine File permissions, but also to give it REPLICATION SLAVE privilege.
Before 2, after modifying Slave machine /etc/my.cnf, slave machine mysql service starts, remember to delete master.info
3, in the show master status or the show slave status is not normal to see .err what to say.
4, on the Slave Mysql Replication work with two threads, I / O thread and the SQL thread. The role of I / O ports from the master 3306 to take over its binlog (master has been modified in any of the contents, the will to modify what is written own binlog waiting slave update), and then writes the local relay- log, and the SQL thread is to read the local relay-log, and then convert it costs Mysql statement can be understood, then sync it to complete this step by step. decision I / O thread is / var / lib / mysql / master.info, but decided SQL thread is /var/lib/mysql/relay-log.info.
5, start the slave, command start slave; restart with restart slave |
|
|
|