I. The master-slave replication process of mysql requires at least two mysql service nodes. The master-slave replication framework of mysql is generally a master-slave multi-slave mode, which is summarized from multiple data centers to the data center mode, the replication process can only be from the master server to the slave server.
I. The master-slave replication process of mysql requires at least two mysql service nodes. The master-slave replication framework of mysql is generally a master-slave multi-slave mode, which is summarized from multiple data centers to the data center mode, the replication process can only be from the master server to the slave server.
I. Master-slave replication of mysql
The replication process requires at least two mysql service nodes. The master-slave replication framework of mysql is generally one master node, multiple slave nodes, and the mode from multiple data centers to data centers is summarized, the replication process can only be implemented from the master server to the slave server.
The master server is responsible for read/write operations while the slave server is only responsible for read operations.
Ii. Master-slave Replication
1. data redundancy, remote disaster recovery, and backup
2. Data read/write Separation
3. Load Balancing of database services
4. High Availability and Failover
5. Upgrade MySQL
Iii. considerations During master-slave Replication
1. Do not mix shi with different storage engines
2. The server-id of the Master/Slave server must be different.
3. Try your best to avoid modifying the slave server database
4. Try to use row-based or hybrid replication to avoid statement-based replication.
5. The master server must start binary logs, while the slave node must enable relay logs, and ensure that the slave server's replication thread is enabled at all times.
6. Copy crashes due to insufficient disk and memory size.
IV. Implementation Process of master-slave Replication
Mysql master-slave replication topology
1. Prepare two mysql-installed nodes xz .200.5 and node2: 172.16.200.7. Modify the Host Name and synchronize the time;
[Root @ xz ~] # Hostname master [root @ master ~] # Crontab-e */1 *****/usr/sbin/ntpdate 172.16.0.1 &>/dev/null [root @ node2 ~] # Hostname slave [root @ slave ~] # Crontab-e */1 *****/usr/sbin/ntpdate 172.16.0.1 &>/dev/null
2. Create a user with the copy permission on the master node and use the authorized user on the slave node to connect to the test;
[Root @ master ~] # MysqlWelcome to the MySQL monitor. Commands end with; or \ g. Your MySQL connection id is 3 Server version: 5.5.33-log MySQL Community Server (GPL )..... Mysql> grant replication slave, replication client on *. * to 'zly '@' 172. 16.200.7 'identified by 'mypass'; Query OK, 0 rows affected (0.35 sec) mysql> flush privileges; Query OK, 0 rows affected (0.09 sec) mysql> show grants for 'zly '@' 172. 16.200.7 '; + certificate -------------- + | Grants Zly@172.16.200.7 | + privileges -------------- + | grant replication slave, replication client on *. * TO 'zly '@ '2017. 16.200.7' identified by password '* failed' | + privileges ------------------------------------------------------------------------------------------- ------------------------------------------------ + 1 row in set (0.03 sec) ...... [root @ slave ~] # Mysql-uzly-pmypass-h 172.16.200.5Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 4 Server version: 5.5.33-log MySQL Community Server (GPL) Copyright (c) 2000,201 3, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or' \ H' for help. type '\ C' to clear the current input statement. mysql>
3. modify the configuration file of the master and slave nodes and restart the mysql service;
[Root @ master ~] # Vim/etc/my. cnflog-bin = mysql-binlog_bin_index = mysql_bin.indexbinlog_format = mixedserver-id = 5 [root @ master ~] # Killall mysqld [root @ master ~] # Ps aux | grep mysqldroot7700 0.0 0.1 103244 832 pts/3 S + grep mysqld [root @ master ~] # Service mysqld restartMySQL server PID file cocould not be found! [FAILED] Starting MySQL... [OK] [root @ master ~] # Service mysqld restartShutting down MySQL. [OK] Starting MySQL... [OK] ...... [root @ slave ~] # Vim/etc/my. cnf # log-bin = mysql-bin # binary logging format-mixed recommended # binlog_format = encoding = 1read_only = 1relay_log = encoding = relay_log.index # required unique id between 1 and 2 ^ 32-1 # defaults to 1 if master-host is not set # but will not function as a master if omittedserver-id = 7 [root @ slave ~] # Killall mysqld [root @ slave ~] # Ps aux | grep mysqldroot8796 0.0 0.1 103244 832 pts/4 S + grep mysqld [root @ slave ~] # Service mysqld restartMySQL server PID file cocould not be found! [FAILED] Starting MySQL... [OK] [root @ slave ~] # Service mysqld restartShutting down MySQL. [OK] Starting MySQL... [OK]
4. view the binary log of the master node and its event location;
Mysql> show master status; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_Do_DB | usage | + usage + ---------- + -------------- + usage + | mysql-bin.000011 | 107 | + usage + -------- + -------------- + ------------------ + 1 row in set (0.00 sec)
5. Copy data from the master node;
[Root @ slave ~] # Mysqlmysql> help change master toName: 'change master to 'Description: Syntax: change master to option [, option]... option: MASTER_BIND = 'interface _ name' | MASTER_HOST = 'host _ name' | MASTER_USER = 'user _ name' | MASTER_PASSWORD = 'Password' | MASTER_PORT = port_num | MASTER_CONNECT_RETRY = interval | MASTER_HEARTBEAT_PERIOD = interval | MASTER_LOG_FILE = 'master _ log_name '| MASTER_LOG_POS = master_log_pos | RELAY_LOG_FILE = 'relay _ log_name' | primary = primary | MASTER_SSL = {0 | 1} | 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 = 'cipher _ list' | bytes = {0 | 1} | IGNORE_SERVER_IDS = (server_id_list) server_id_list: [server_id [, server_id]... ...... mysql> change master to-> master_host = '2017. 16.200.5 ', master_user = 'zly', master_password = 'mypass', master_port = 3306, master_log_file = 'mysql-bin.000011 ', master_log_pos = 107; Query OK, 0 rows affected( 0.07 sec)
6. Start the slave server to copy the thread, view the status, and view the started thread;
