MySQL database master-slave replication _ MySQL

MySQL database Master-Slave replication MySQL supports one-way, asynchronous replication, one as the Master server, multiple as the Slave server.
MySQL master-slave replication usually uses A single-star structure, such as A --> B, A --> C. of course, you can also use A chain structure, such as A --> B --> C.
The master server writes updates to the binary log file (log-bin) and passes the updates to the slave server for another execution. In general, once the log file of the master server is updated, the slave server immediately starts replication, as long as the network and hardware performance is sufficient, this latency will be very small (if it tends to be 0, it is almost synchronous ).
Both the master and slave servers are online, allowing external services at the same time, which is also equivalent to a load balancing (read-only ).
The slave server can be set to read-only to avoid inconsistency caused by misoperations. Therefore, the database query and update can be separated (A is readable and writable, and B/C is read-only ).

######################################## ######################################

System environment:
RHEL 5.5 [2.6.18-194. el5]

Software environment:
Mysql-server-5.0.77-4.el5_4.2
Mysql-5.0.77-4.el5_4.2 with RHEL5 built-in RPM Package installation, master, slave server are installed, the default Database Directory is located in/var/lib/mysql/

######################################## ######################################

I. MySQL master server (server105, 192.168.4.105) 1. backup existing database method 1. online hot backup:
[Root @ server105 ~] # Mysqldump-u root-p -- all-databases>/tmp/mysql-master. SQL method 2, binary cold backup:
[Root @ server105 ~] # Service mysqld stop # // You can also change it to locked (mysql> flush tables with read lock ;)
[Root @ server105 ~] # Cd/var/lib/mysql/
[Root @ server105 mysql] # tar zcpvf/tmp/mysql-master.tar.gz ./
[Root @ server105 mysql] # service mysqld start
2. modify the configuration of my. cnf and restart the mysqld service.
[Root @ server105 ~] # Cp/etc/my. cnf/etc/my. cnf. origin
[Root @ server105 ~] # Vim/etc/my. cnf
[Mysqld]
....
Log-bin = mysql-bin # // use a binary log file
Server-id = 1 # // specify the server id. each server involved in replication cannot be the same
Innodb_flush_log_at_trx_commit = 1 # // improve InnoDB replication tolerance and consistency
Sync-binlog = 1 # // enable log synchronization
... [Root @ server105 ~] # Service mysqld restart
3. create a duplicate account
For accounts that only execute REPLICATION, you only need to grant the replication slave permission;
If you want the slave server to use the LOAD method to import the MASTER database (load data from master and load table from master), you also need to grant the SUPER and RELOAD global permissions and SELECT permissions on the relevant database tables. During the LOAD operation, tables without the SELECT permission will be ignored, which may cause inconsistency between the master and slave nodes. [Root @ server105 ~] # Mysql-u root-p
Mysql> grant replication slave on *. * TO 'replicater '@ '192. 192. %' identified by 'pwd @ 100'; or [root @ server105 ~] # Mysql-u root-p
Mysql> grant replication slave, SUPER, RELOAD, select on *. * TO 'replicater '@ '192. 192. %' identified by 'pwd @ 100 ';
4. confirm the status of the master database
Write down the log file name (mysql-bin.000005), offset (3586), use mysql> show master status when the first replication from the server;
+ -----------------+ ------------------ + --------------------- + -------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ --------------------- + ------------------ + --------------------- + ---------------------- +
| Mysql-bin.000005 | 3586 |
+ --------------------- + ------------------ + --------------------- + ---------------------- +
1 row in set (0.00 sec) II. MySQL slave server (server205, 192.168.4.205) 1. import existing database (master database backup) method 1. online Hot Import:
[Root @ server205 ~] # Scp 192.168.4.105:/tmp/mysql-master. SQL/tmp/
[Root @ server205 ~] # Mysql-u root-p </tmp/mysql-master. SQL method 2, binary Cold export:
[Root @ server205 ~] # Service mysqld stop
[Root @ server205 ~] # Rm-rf/var/lib/mysql/* # // This step is optional
[Root @ server205 ~] # Scp 192.168.4.105:/tmp/mysql-master.tar.gz/tmp/
[Root @ server205 ~] # Tar zxpvf/tmp/mysql-master.tar.gz-C/var/lib/
[Root @ server205 ~] # Service mysqld start
2. modify the configuration of my. cnf and restart the mysqld service.
[Root @ server205 ~] # Cp/etc/my. cnf/etc/my. cnf. origin
[Root @ server205 ~] # Vim/etc/my. cnf
[Mysqld]
....
Log-bin = mysql-bin # // use a binary log file
Server-id = 2 # // specify the server id. each server involved in replication cannot be the same
Innodb_flush_log_at_trx_commit = 1 # // improve InnoDB replication tolerance and consistency
Sync-binlog = 1 # // enable log synchronization [root @ server205 ~] # Service mysqld restart
3. start slave database replication 1) first copy operation
[Root @ server205 ~] # Mysql-u root-p
Mysql> change master to MASTER_HOST = '192. 168.4.105 ',
MASTER_USER = 'replicater ',
MASTER_PASSWORD = 'pwd @ 123 ',
MASTER_LOG_FILE = 'MySQL-bin.000005 ',
MASTER_LOG_POS = 3586; mysql> slave start; # // enable SLAVE database replication 2) view connection information
[Root @ server205 ~] # Head/var/lib/mysql/master.info # // The connection information is stored in this file and provided to the mysqld service call.
14
Mysql-bin.000005
3586
192.168.4.105
Replicater
Pwd @ 123
3306
60
0
... 3) confirm the replication status
Mysql> show slave status/G
* ****************************** 1. row **********************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.105
Master_USER: replicater
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 3586
Relay_Log_File: mysqld-relay-bin-bin.000016
Relay_Log_Pos: 372
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes # // The IO thread of the slave server is running.
Slave_ SQL _Running: Yes # // The SQL thread of the slave server is running
....
....
Seconds_Behind_Master: 0 # // This option can reflect master --> slave replication latency. 0 is the best state (no latency)
1 row in set (0.00 sec)
4. adjust the service script and restart the mysqld service.
You can modify the execution statement of the start () function to control the startup options of the mysqld service. For example:
-- Log-slave-updates = 1 # // record updates from the database to allow chain replication
-- Read-only # // Set slave database as read-only, which can only be executed by the slave server thread or user with SUPER permission
-- Relay-log = mysqld-relay-bin # // use a fixed relay log file
-- Report-host = server205 # // host name or IP address reported to the master server
-- Slave_compressed_protocol = 1 # // enable compression during the replication process. if this option is enabled, both the master and slave servers should be added.
-- Replicate-do-db = mysql # // only copy the specified database. other databases will be ignored. this option can be set to multiple (if omitted, all databases will be copied)
-- Replicate-do-db = mytestdb
-- Replicate-ignore-db = test # // do not copy (ignore) the specified database. this option can also be set to multiple. you can use either do or ignore.
-- Skip-slave-start # // skip replication when the server starts. you need to manually START SLAVE
-- Slave-net-timeout = 60 # // The waiting time before retrying again when the slave server network is interrupted (60 seconds by default)
-- Master-connect-retry = 60 # // The waiting time before retrying again when the master server connection is lost (60 seconds by default)
... [Root @ server205 ~] # Vim/etc/init. d/mysqld
....
Sart (){
....
EXTRA_ARGS = "-- log-slave-updates = 1 -- read-only -- relay-log = mysqld-relay-bin -- report-host = server205 -- slave_compressed_protocol = 1 -- replicate-do- db = mysql -- replicate-do-db = mytestdb"
/Usr/bin/mysqld_safe -- datadir = "$ datadir" -- socket = "$ socketfile "/
-- Log-error = "$ errlogfile" -- pid-file = "$ mypidfile "/
-- User = mysql $ EXTRA_ARGS>/dev/null 2> & 1 &
Ret = $?
....
}
... [Root @ server205 ~] # Service mysqld restart
III. verify master/slave synchronization results 1. perform the update operation on the master server
For Databases configured to be replicated, you can perform various update operations such as creating/deleting databases, creating/Deleting tables, and adding/modifying/deleting table records. 2. observe database changes on the slave server
The confirmation result of the SELECT query is consistent with the database operation result of the master server, which is almost real-time.