Mysql master-slave replication comprehensive analysis and application drills

The built-in Mysql replication function is the basis for building large and high-performance applications.

The built-in Mysql replication function is the basis for building large and high-performance applications.

Master Server:

[Root @ node1 ~] # Ssh-keygen-t rsa-p' [root @ node1 ~] # Root@172.16.18.9 for ssh-copy-id-I. ssh/id_rsa.pub

Slave Server:

[Root @ node2 ~] # Ssh-keygen-t rsa-p' [root @ node2 ~] # Root@172.16.18.7 for ssh-copy-id-I. ssh/id_rsa.pub

2. Edit the hosts file of the Master/Slave Server:

[Root @ node1 ~] # Vim/etc/hosts ##### Add the following two items: ############## 172.16.18.7node1.magedu.com node1172.16.18.9node2.magedu.com node2 #### save and copy it to node2: ######## [root @ node1 ~] # Scp/etc/hosts 172.16.18.9:/etc

This is not detailed here. Please refer

(1) create a user # groupadd-g 306 mysql # useradd-g mysql-u 306-r mysql (2) and create a data directory: # mkdir/mydata/data-pv (3). Change the data directory permission: # chown-R mysql. mysql/mydata/data (4), unzip Mysql CREATE link # tar xfmysql-5.5.33-linux2.6-x86_64.tar.gz-C/usr/local # ln-svmysql-5.5.33-linux2.6-x86_64 mysql (5) change owner group # cd mysql # chown-R root: mysql. /* (6) initialize mysql # scripts/mysql_install_db -- user = mysql -- datadir =/mydata/data (7), create a STARTUP script, and grant permissions # cpsupport-files/mysql. server/etc/rc. d/init. d/mysqld # chmod + x/etc/rc. d/init. d/mysqld (8), create and modify configuration file # cp support-files/my-large.cnf/etc/my. cnf # vim/etc/my. add the following lines to cnf to specify the storage location of mysql data Files: datadir =/mydata/data (9) and change the environment variable # vim/etc/profile. d/mysql. sh Add: exportPATH =/usr/local/mysql/bin: $ PATH #. /etc/profile. d/mysql. sh (10), add it to the service list, and start mysql # chkconfig -- addmysqld # service mysql start

5. Configure the master server:

(1) edit the configuration file:

[Root @ node1 ~] # Vim/etc/my. cnfserver-id = 1 # Lines 60 identify your id log-bin = mysql-bin #52 lines of binary logs. By default, binlog_format = mixed #55 lines start in hybrid mode.

(2) create a copy account permission:

[Root @ node1 ~] # Mysqlmysql> grant replicationclient, replication slave on *. * TO rpuser @ '192. 16. %. % 'identified by 'rpuser'; mysql> FLUSHPRIVILEGES; # refresh permission

6. Configure slave server:

(1) edit the configuration file:

[Root @ node2 ~] # Vim/etc/my. cnfserver-id = 20 # Line 60 identifies your id number, it must not be the same as the master server. # log-bin = mysql-bin #52 lines of binary logs are closed # binlog_format = mixed #55 lines of logs are closed in hybrid mode #### set slave server to read-only mode ###### read-only = 1 # note that the slave server cannot have a write mode ##### add relay logs ############ #### relay-log =/mydata/data/relay-mysql # relay log relay-log-index = relay-mysql.index # relay log index file

[Relay-log records that the binary log of the master server is read from the server I/O thread During the replication process and recorded to the local file of the slave server, the SQL thread then reads the relay-log Content and applies it to the slave server.]

(2) log on to the mysql viewing thread:

View startup threads

Mysql> SHOWPROCESSLIST; + ---- + ------ + ----------- + ------ + --------- + ------ + ------- + ------------------ + | Id | User | Host | db | Command | Time | State | Info | + ---- + ------ + ----------- + ------ + --------- + ------ + ------- + ---------------- + | 1 | root | localhost | NULL | Query | 0 | NULL | show processlist | + ---- + ------ + ----------- + ------ + --------- + ------ + ------- + ------------------ +

The result shows that no threads have been started yet.

(3) configure the connection Server:

Command Parameter Parsing:

Mysql> helpCHANGE MASTER TOMASTER_BIND = 'interface _ name' # bind the control to that interface | MASTER_HOST = 'host _ name' # MASTER server address | MASTER_USER = 'user _ name '# master Server user (created just now) | MASTER_PASSWORD = 'Password' # master server password | MASTER_PORT = port_num # Working port | MASTER_CONNECT_RETRY = interval # retry time | MASTER_HEARTBEAT_PERIOD = interval # interval to check whether the master server lags behind, whether the master server is online | MASTER_LOG_FILE = 'master _ log_name '# specifies which binary file to copy | MASTER_LOG_POS = master_log_pos # specifies the Event Location of the binary file | RELAY_LOG_FILE = 'relay _ log_name '# relay log | RELAY_LOG_POS = relay_log_pos | MASTER_SSL = {0 | 1} # use SSL function | MASTER_SSL_CA = 'Ca _ file_name' | MASTER_SSL_CAPATH = 'Ca _ directory_name '| MASTER_SSL_CERT = 'cert _ file_name '| MASTER_SSL_KEY = 'key _ file_name' | MASTER_SSL_CIPHER = 'ipher _ list' | MASTER_SSL_VERIFY_SERVER_CERT = {0 | 1} | IGNORE_SERVER_IDS = (server_id_list) # ignore some ID numbers

Configure the slave server to connect to the master server:

Mysql> changemaster to MASTER_HOST = '192. 16.18.7 ',-> MASTER_USER = 'rpuser',-> MASTER_PASSWORD = 'rpuser ';

(4) Start the slave server thread:

Mysql> start slave;

(5) view the slave server's working status

Mysql> showslave status \ G **************************** 1. row *************************** Slave_IO_State: waiting formaster to send event # I/O status; accept master server sending status Master_Host: 172.16.18.7 # master server user account Master_User: rpuser # user Master_Port: 3306 # default listening port Connect_Retry: 60 # Retry Interval Master_Log_File: mysql-bin.000002 # Read Binary Log File Read_Master_Log_Pos: 107 # Event Location Relay_Log_File: relay-mysql.000003 # Relay_Log_Pos: 253Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: yes # the I/O thread has started prepare: Yes # the SQL thread has started prepare: Prepare: Replicate_Do_Table: Timeout: Last_Errno: 0Last_Error: Skip_Counter: Timeout: Prepare relay_log_space: Timeout: failed: Until_Log_Pos: Failed: Master_SSL_CA_Path: Master_SSL_Cert: Failed: Master_SSL_Key: Seconds_Behind_Master: 0 # Time lag behind the master server: Failed: Master_Server_Id: 1

7. Test