MySQL Master/Slave setting notes

Create Table persons
(
Id int unsigned not null auto_increment,
Lastname varchar (255 ),
Firstname varchar (255 ),
Address varchar (255 ),
City varchar (255 ),
Created datetime,
Primary Key (ID)
) Type = MyISAM auto_increment = 1;
Insert into persons (lastname, firstname, created) values ('hunaming', 'yue', now ());

// Master server
[Mysqld]
Log-bin = mysql-bin
Server-id = 1
BINLOG-ignore-DB = MySQL, test
# BINLOG-do-DB = vBB

// Create an account for use by the slave server
Grant all on *. * To rep_slave @ '%' identified by 'job ';
Grant replication slave, replication client on *. * To rep_slave @ '%' identified by 'joeyue ';
Flush privileges;

Slave Server
Log-bin = mysql-bin
Server-id = 2
Master-host = 192.168.1.104
Master-user = rep_slave
Master-Password = joeyue
Master-Port = 3306
Master-connect-retry = 60
Log-slave-Updates
# Replicate-do-DB = foxshare // specify the database to be synchronized
Insert into persons (ID, lastname, firstname, created) values (2, 'huaming', 'yue ', now ());

// Note
Share the same database and Table Structure

<? PHP
$ Link = mysql_connect ('192. 168.1.104: 100', 'user', 'Password ');
If (! $ Link ){
Echo 'failed ';
} Else {
Mysql_select_db ('persons ', $ link );
For ($ I = 0; $ I <50; $ I ++ ){
Mysql_query ("insert into persons (ID, lastname, firstname, created) values (2, 'hunaming', 'yue ', now ());");
}
}
Mysql_close ($ link );
?>

The specific configuration process is as follows:
MASTER:
1. Create a copy permission account for the slave server (slave)
Mysql>Grant replication slave on *. * To 'slave '@ '1970. Route 92.110' identified by 'slave ';
PS: Replication slave is a single copy permission user name slave password slave
2. Lock the host database and export all data in the current locked state to the slave machine for basic data synchronization before Replication
Mysql> flush tables with read lock;
Mysql> exit
[Root @ %] # mysqldump-uroot-P test> test. SQL
3. query the database files and locations of the current host.
Mysql> show Master status;
------ + ---- + ----- + ------ +
| File | position | binlog_do_db | binlog_ignore_db |
+ ------ + ---- + ----- + ------ +
| Mysql-bin.000041 | 1509 |
+ ------ + ---- + ----- + ------ +
PS: record these two values (file name, current location), which will be used for configuring slave host connection later.

SLAVE:
1. Create the database test and import the SQL file generated by the host master to the database test.
2. Turn off slave and read the host log file and location information to the slave machine.
Mysql> stop slave;
Mysql> change master
-> Master_log_file = 'mysql-bin.20.41 ′,
-> Master_log_pos = 1509;
Mysql> Start slave;
Mysql> show slave status \ G
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_io_state: Waiting for Master to send event
Master_host: 172.20.92.108 # Host IP Address
Master_user: slave # copy an account
Master_port: 3306
Connect_retry: 60
Master_log_file: mysql-bin.000041 # host Log File
Read_master_log_pos: 1509 # host Log File Location
Relay_log_file: mysqld-relay-bin.000007 # slave relay Log File
Relay_log_pos: 235 # relay Log File Location
Relay_master_log_file: mysql-bin.000041
Slave_io_running: Yes ## connecting the slave I/O thread to the host
Slave_ SQL _running: yes # process updated logs from the SQL thread
Replicate_do_db:
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: 1509
Relay_log_space: 235
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.00 Sec)

Next, unlock the master to allow updates to the host database.

MASTER:

Mysql> unlock tables;