MySQL replication (the basis of a highly available architecture scenario)

MySQL replication: The process of replicating all changes on one DB instance to another database server instance
Characteristics:
1. No change does not matter copy; change is the root of replication and the data source
2. All changes: means that all changes can be replicated, or partial changes can be replicated in all changes according to the business requirements select the partial library and partial table replication
Replicated scenes:
1. Data storage disaster
2. Requirements: Create a data server to do data testing and analysis
3. Load Balancing
4. Basics of high-availability architecture scenarios when replicating

MySQL High-availability architecture features
1. Detection and elimination of database failures
2. master-Slave Database switching
3. Backup and protection of data

MySQL High-availability architecture common scenarios
1. Dual Master Auto/manual switch
2.altas,opneproxy read-Write separation scheme
3.MMM Architecture
4.MHA Architecture
5.DRDB High-availability architecture
6.mycat Highly Available Shard architecture
7.mysql NDB cluster cluster architecture
8.percona xtradb cluster (PXC) cluster architecture
9.mysql Fabric high-availability architecture

MySQL Replication configuration
Synchronous replication Steps
1. Configure the master server
2. Configuring Slave instances
3. Configure slave replication to connect to master

1. Configure the Master service
Log-bin=/var/lib/mysql/binlog/mysql-bin.log (Open binary file and binary file location, note is the file is not a folder Mysql-bin is the beginning of the binary log format, note the Binlog directory to the MySQL user's rights)
server-id=108 (server unique ID, must be set, if not set, can be accessed by entering MySQL after show Binlog events in ' mysql-bin.000001 '; view)

Mysql> Show Binlog Eventsinch 'mysql-bin.000001';+------------------+-----+-------------+-----------+-------------+---------------------------------------+| Log_name | Pos | Event_type | server_id | End_log_pos | Info |+------------------+-----+-------------+-----------+-------------+----------------- ----------------------+| Mysql-bin.000001|4| Format_desc |1| -| Server ver:5.6. *-log, Binlog ver:4|+------------------+-----+-------------+-----------+-------------+---------------------------------------+

Restart MySQL service after Setup

Mysql>show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| Mysql-bin.000001|143|| Mysql-bin.000002| -|+------------------+-----------+MySQL> Show Binlog Eventsinch  'mysql-bin.000002';+------------------+-----+-------------+-----------+-------------+---------------------------------------+| Log_name | Pos | Event_type | server_id | End_log_pos | Info |+------------------+-----+-------------+-----------+-------------+----------------- ----------------------+| Mysql-bin.000002|4| Format_desc |108| -| Server ver:5.6. *-log, Binlog ver:4|+------------------+-----+-------------+-----------+-------------+---------------------------------------+MySQL>show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+------------------+-------------------+| Mysql-bin.000002| -|                  |                   | |+------------------+----------+--------------+------------------+-------------------+

/etc/my.cnf Example of Master

[Mysqld]datadir=/var/lib/Mysqlsocket=/var/lib/mysql/Mysql.socklog-bin=/var/lib/mysql/binlog/mysql-Bin.logserver-ID=108# Disabling Symbolic-links are recommended to prevent assorted security riskssymbolic-links=0# RecommendedinchStandard MySQL Setupsql_mode=No_engine_substitution,strict_trans_tables[mysqld_safe]log-error=/var/log/Mysqld.logpid-file=/var/run/mysqld/Mysqld.pidexplicit_defaults_for_timestamp=true

2. From the configuration my.cnf of the library

Server-id=251

Open from library process
Start slave;
If there is a warning
Show warnings;

3. Configure the slave connection to master replication

Create an account with copy permissions on the main library

Grant Replication  on  *. *  to [Email protected] ' % ' identified by  'rep123456'Privileges ;

Executing from server MySQL

Change Master toMaster_host='192.168.1.250', Master_port=3306, Master_user='Rep', Master_password='rep123456', Master_log_file='mysql-bin.000003', Master_log_pos=106;

Master_log_file indicates which binary file to start with, and where Master_log_pos indicates where to start

For example, the main library where I do the test is 192.168.1.250 from the library is 192.168.1.251

View from library status

Show Slave Status\g

Slave_io_state:master_host:192.168.1.250master_user:rep Master_port:3306Connect_retry: -Master_log_file:mysql-Bin.000003Read_master_log_pos:106Relay_log_file:mysqld-Relay-Bin.000001Relay_log_pos:4Relay_master_log_file:mysql-Bin.000003Slave_io_running:no Slave_sql_running:no Replicate_do_db:replicate_ig Nore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_ Table:last_errno:0Last_error:skip_counter:0Exec_master_log_pos:106Relay_log_space:106until_condition:none Until_log_file:until_log_pos:0Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_ Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:NULLmaster_ssl_verify_server_cert:no Last_io_errno:0Last_io_error:last_sql_errno:0Last_sql_error:

View Code

Main parameters

Slave_io_running:no
Slave_sql_running:no

Open from library process
Start slave;

Show Slave Status\g

Then:

Slave_io_running:yes
Slave_sql_running:yes

Indicates that the configuration was successful, insert data in the corresponding table of the main library, and have it from the library!

MySQL replication (the basis of a highly available architecture scenario)