Simple setup of master-slave MySQL

Simple setup of master-slave MySQL

192.168.190.128 "====" 192.168.190.129
Master ---- slave
Slave ---- master
 
1. Configure the my. cnf file on the master 128 and add the following parameters:
Server-id = 1
Log-bin = mysql-bin
Log-salve-updates
Sync_binlog = 1
Auto_increment_increment = 2
Auto_increment_offset = 1
 
Restart mysql
[Root @ calvin1 ~] #/Etc/init. d/mysqld start
Starting MySQL: [OK]
[Root @ calvin1 ~] # Mysql-u root-p123456
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 6
Server version: 5.0.77-log Source distribution
 
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
 
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Calvin |
| Calvin2 |
| Mysql |
| Sampdb |
| Test |
| Testdb |
+ -------------------- +
7 rows in set (0.00 sec)
 

2. Configure the my. cnf file on the master 129 and add the following parameters:
Server-id = 2
Log-bin = mysql-bin
Log-salve-updates
Sync_binlog = 1
Auto_increment_increment = 2
Auto_increment_offset = 2
 
Restart mysql
[Root @ calvin2 ~] #/Etc/init. d/mysqld start
Starting MySQL: [OK]
[Root @ calvin2 ~] # Mysql-u root-p123456
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 7
Server version: 5.0.77-log Source distribution
 
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
 
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Calvin |
| Calvin2 |
| Mysql |
| Sampdb |
| Test |
| Testdb |
+ -------------------- +
7 rows in set (0.00 sec)
 

3. Create a replication account on master 128:
Mysql> grant replication slave, file on *. * to mysync @ '192. 168.190.129 'identified by '123 ';
Query OK, 0 rows affected (0.00 sec)
 
Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
Add read-only locks to all tables:
Mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
 
View the binary file of the master, pos Number:
Mysql> show master status \ G;
* *************************** 1. row ***************************
File: mysql-bin.000004
Position: 906
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
 
ERROR:
No query specified
 
Mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
 
Mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 

4. Create a replication account on master 129:
Mysql> grant replication slave, file on *. * to mysync @ '192. 168.190.128 'identified by '123 ';
Query OK, 0 rows affected (0.00 sec)

Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
Add read-only locks to all tables:
Mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
 
View the binary file of the master, pos Number:
Mysql> show master status \ G;
* *************************** 1. row ***************************
File: mysql-bin.000001
Position: 318
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
 
ERROR:
No query specified
 
Mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
 
Mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
 
 
 
5. Modify synchronization parameters:
Configure the synchronization parameters of master 3rd Based on the binary file and pos number read in Step 1:
Mysql> change master
-> Master_host = '192. 168.190.129 ',
-> Master_user = 'mysync ',
-> Master_password = '123 ',
-> Master_log_file = 'mysql-bin.000001 ',
-> Master_log_pos = 318;
Query OK, 0 rows affected (0.01 sec)
 
Mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
Configure the synchronization parameters of master 4th Based on the binary file and pos number read in Step 1:
Mysql> change master
-> Master_host = '192. 168.190.128 ',
-> Master_user = 'mysync ',
-> Master_password = '123 ',
-> Master_log_file = 'mysql-bin.000004 ',
-> Master_log_pos = 906;
Query OK, 0 rows affected (0.01 sec)
 
Mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
 

6. view the slave synchronization status (check whether Slave_IO_Running and Slave_ SQL _Running are both Yes ):
Master128:
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.190.129
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 318
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
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: 318
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)
 
ERROR:
No query specified
 
Master129:
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.190.128
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 906
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
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: 906
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)
 
ERROR:
No query specified