MySQL Replication architecture and optimization

MySQL Replication architecture and optimization

########## #原理 ###########
1. The primary server writes the SQL statements of the updated data (for example, Insert,update,delete, etc.) to the
Binary (opened by the Log-bin option). This binary file is maintained by an index file trace.

2. From the server connection (use I/O thread connection) master server, the location of the last update to notify
The primary server. The master server will then post all updates from the location that you know from the server
Send to ' from server ' (using Binlog dump thread) and then ' re-use I/O from server '
The thread reads the data sent by the Binlog dump line Cheng and copies the data to the local ' relay binary
System files '. Finally, the SQL thread reads ' relay binaries ' and performs the updates in it.

Note: MySQL replication is done by three threads, one is the Binlog dump thread on the primary server;
Yes, from the I/O thread on the server (used to connect and read the Master service update and copy to the relay binary
and SQL threads (used to read the relay binary logs and perform updates).

#######################################
# master-Slave Architecture #
#######################################

############ #配置 #############
Note: The mysql-5.5.28 binary package is used here. The installation process is slightly. Direct-to-master replication configuration
# #主服务器
1. Change/ETC/MY.CNF:
Server-id = 1 #设置服务器唯一标识
Log-bin=mysql-bin #开启二进制日志功能
2. Add a replication User:
GRANT REPLICATION client,replication SLAVE to ' repl ' @ ' 192.168.1.103 '
Identified by ' 123 ';
# #从服务器
1. Change/ETC/MY.CNF:
Server-id = 2 #同主服务器
Relay-log=relay-bin #开启中继日志
Relay-log-index=relay-bin.index #开启跟踪中继日志的索引, if this option is not set
The index file is also automatically generated by the item system.
2. Start MySQL and set it to slave server
1. Mysql-uroot-p
2. Change MASTER to master_host= ' 192.168.1.102 ',
Master_user= ' Repl ',
Master_password= ' 123 ',
master_port= ' 3306 ';
3. START SLAVE;
4. SHOW SLAVE STATUS \g; If slave_io_running: and slave_sql_running: all displayed
Yes indicates a successful configuration from the server.
Note: show SLAVE STATUS \g; Displays the Seconds_behind_master in the message: represents the From service
The time interval between the data and the primary server.
5. Test: Create a table or database on the primary service to see if you have the same tables and databases on the server.
If so, the master-slave replication is successfully built.
############ #安全 ############
# #阻止写从服务器
1. Modify/ETC/MY.CNF
[Mysqld]
Read-only = 1 # This option works only for normal users and is not valid for users with super privileges.
2. FLUSH TABLES with Read LOCK, #为全局读锁命令, at this time other operations cannot be performed except for the read operation
# #实现半同步
Description:---from, to asynchronous mode. MySQL supports semi-synchronous mode replication starting from 5.5, semi-synchronous plug-in for Semisync, storage
Under the/usr/local/mysql/plugin.
1. On the primary server, install the Semisync plugin
Change INSTALL rpl_semi_sync_master SONAME ' semisync_master.so ';
To see if the installation was successful:
SHOW PLUGINS; #若有rpl_semi_sync_master installation is successful.
Enable the semi-sync feature and set the timeout time:
SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_master_timeout=1000; #单位是ms if the half-sync is set in this
cannot be synchronized in time, it automatically drops back into asynchronous mode.
Note: The Shut up setting is permanently valid, and the above two entries are written to the MY.CNF [mysqld].

2. On the slave server, install the Semisync plugin
Change INSTALL rpl_semi_sync_slave SONAME ' semisync_slave.so ';
To see if the installation was successful:
SHOW PLUGINS; #若有rpl_semi_sync_slave installation is successful.
Enable the semi-sync feature and set the timeout time:
SET GLOBAL rpl_semi_sync_slave_enabled=1;
Restart Slave:
Stop slave;
Start slave;

3. Detect if the half-sync function is already in effect
SHOW STATUS like ' rpl_% ';
If the value of rpl_semi_sync_master_clients is not 0, the semi-synchronous function is already in effect.

# #如何让从服务器的mysql服务在启动的时候, do not automatically start from the service thread?
Description: The thread is automatically started by the server at boot time because of the presence of Master.info and relay-log.info files.
   Master.info Records the parameters passed by the change Master to command; Relay-log.info records the current use of the server from the
   The location of the relay log and the binaries and locations copied from the primary server.
   
 1. On the slave server, disable the auto-start thread
   change my.cnf, adding the following options:
  [mysqld]
  skip-slave-start=1
 
# #数据库复制过滤
  Primary server:
 1.[ MYSQLD]
   binlog-do-db=test   #只复制test数据库, equivalent to whitelist.
   Binlog-ignore-db=mysql #除了mysql数据库外不复制外, the others are copied, equivalent to the blacklist.
   Note: These two items are not used at the same time, if they exist at the same time, the whitelist will take effect. However, there is a flaw in filtering on the primary server, which is that any
   databases that are not involved are not recorded in the binary log. Therefore, most of the cases do not filter on the primary server.

From the server:
1.[MYSQLD]
Replicate-do-db=test1
Replicate-ignore-db=test1

Replicate-do-table=test2.t1
Replicate-ignore-table=test2.t2

replicate-wild-do-table=test3.ta%
replicate-wild-ignore-table=test3.tb%

# #防止事务提交和写入日志, during a server crash issue
Primary server:
1. [Mysqld]
Sync_binlog=1 #每次事件后立即同步到磁盘上的二进制日志文件中
Innodb_flush_logs_at_trx_commit=1 #

#######################################
# Main Master Architecture #
#######################################
Description: The main master architecture, that is, the server mutual primary from. The configuration is basically the same as the master. The key here is if
The Auto_incremnet keyword is used in the table of the database, you need to set the Auto-increment-increment
and auto-increment-offset two items to prevent key-value collisions.
# #主服务器
1. GRANT REPLICATION client,replication SLAVE to ' t1 ' @ ' 192.168.1.103 '
Identified by ' 123 ';
2. [Mysqld]
server-id=10
Log-bin=mysql-bin
auto-increment-increment=2
Auto-increment-offset=1
3. Mysql-uroot-p
4. Change MASTER to master_host= ' 192.168.1.102 ',
master_user= ' T2 ',
Master_password= ' 123 ',
master_port= ' 3306 ';

# #从服务器
1. GRANT REPLICATION client,replication SLAVE to ' T2 ' @ ' 192.168.1.102 '
Identified by ' 123 ';
2. [Mysqld]
server-id=10
Log-bin=mysql-bin
auto-increment-increment=2
Auto-increment-offset=1
3. Mysql-uroot-p
4. Change MASTER to master_host= ' 192.168.1.103 ',
master_user= ' T1 ',
Master_password= ' 123 ',
master_port= ' 3306 ';


################ #MySQL复制架构解决方案 ###############
1.---from (solve the problem of high coupling with application)
1. Divided into three layers:
1. Read/write separators, products are: MySQL Proxy and Amoeba
2. Primary server
3. From the server

2. Divided into four layers:
1. read/write Separators
2. Primary server
3. Pseudo slave server (engine blackhole)
4. From the server

2. master-to-master (resolves data inconsistencies when updating data)
1. Active/Passive mode
That is, set the two host Server-id to the same value.
Product: Mmm,multi Master Manager

#################### #故障解决 ################
# #解决: Cannot start from server when an error occurs
1. SET GLOBAL sql_slave_skip_counter = 1; #此语句可以跳过来自主服务的下一个语句
START SLAVE;
or 2. Use the Pt-slave-restart tool from the Percona-toolkit package.

# #解决: Inconsistent data
1. Check for consistent use:
Pt-table-checksum #此工具四种功能: 1. Check master/Slave data
2. Monitoring replication delay Time
3. System overhead is minimal
4. Check Data consistency
2. Repair inconsistencies using:
Pt-table-sync


>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>
##################### #MySQL的优化 #######################
# #技巧
1. Use regular expression RegExp to remove matching data
Example: SELECT name,email from T WHERE email REGEXP ' @126[.,]com$ ';
If you use the like method to query
Example: SELECT name,email from T WHERE email like '%126.com ' or email like '%126,com ';
Note: One disadvantage of using regular rather than like is that the overhead of system resources will be greater.

2. Use rand () to randomly extract data
Example: SELECT * from T ORDER by RAND ();
SELECT * from T ORDER by RAND () LIMIT 3;

3. Use GROUP by with ROLLUP to further group aggregated data.
Example: SELECT cname,pname,count (CNAME) from the demo GROUP by Cname,pname with ROLLUP;
Note: With ROLLUP cannot be used in conjunction with the ORDER by

# #优化
I. Optimizing SQL statements Common commands
1. Query the execution frequency of various SQL using the show status command.
SHOW [session| GLOBAL] STATUS;
Where: SESSION (default) indicates the current connection.
Global indicates that the database has been started since.
@@ 主要 queries for parameters that begin with COM:
SHOW STATUS liek ' com_% '; #Com_XXX表示每个XXX语句执行的次数
@@ 需要 main COM-based arguments that need to be viewed
Com_select: Number of times a select operation is performed, only one query is accumulated plus 1
Com_update: Number of update operations performed
Com_insert: The number of times the insert operation is performed, only once for bulk inserts
Com_delete: Number of delete operations performed
Note: The above parameters are for all engines.
@@ 以下 Following are for InnoDB storage engines only.
Innodb_rows_read: Number of times the select operation was performed
innodb_rows_updated: Number of update operations performed
innodb_rows_inserted: Number of insert operations performed
innodb_rows_deleted: Number of delete operations performed
Note: The above number of operations for InnoDB is the number of rows that affect the data, not the number of corresponding statements.
@@ 其他 Important parameters
Connections: Number of connections to MySQL, both successful and unsuccessful.
Uptime: The number of seconds that the server has been working.
Slow_queries: Number of slow queries. #可通过SHOW VARIABLES like '%slow_queries% '; see if Open
2. Locating SQL statements that perform less efficiently
1.explain (or describe) SELECT * from table where id=1000;
2. Optimizing SQL statements
1. Query Slow query log
2. Parsing query statements
3. Determine if you want to index and index whether you can use the

3. Index optimization
1. Add an index, mainly on the field used after Where,having,group By,oreder by.
2. When using like, do not put the% wildcard in front, otherwise the index cannot be used.
3. When using OR and and, the index is used for all two conditions before and after, otherwise the index will not be used
4. The index cannot be used if the value of the given condition expression has a different data type than defined
5. View index usage: show STATUS like ' handler_read% ';
The parameter that is displayed: The value of Handler_read_key, which indicates the number of times the index was read.
The higher the value of the Handler_read_rnd_next, the more columns you need to add the index to.

4. Table optimization
1. Analysis and checklist
CHECK TABLE T1; #检查表t1是否有错误
2. Optimize table space
OPTIMIZE TABLE T1; #最好在非工作时间使用
5. Common SQL Optimizations
1. Import and export optimization
@@ 导出 using: SELECT * from table to OUTFILE '/tmp/table.txt ';
@@ 导入 using: LOAD DATA INFILE '/tmp/table.txt ' into table table;
2. Close the index to make the import faster
[Email protected]@ close index: ALTER TABLE tbl_name DISABLE KEYS;
@@ 导入 Data
@@ 开启 Index: ALTER TABLE tbl_name enable KEYS;
Note: The above only for MyISAM table data import can improve speed, InnoDB invalid
[Email protected]@ close unique index: SET unique_checks=0
@@ 导入 Data
@@ 恢复 Unique index: SET Unique_checks=1
Note: If you can determine the uniqueness of the data, you can use the turn off unique index to increase the speed. Otherwise, it is not recommended to close.
3. Optimization of data import for InnoDB table types
1. Increase the import speed by arranging the imported data in the order of the primary key
[Email protected]@ off Auto-commit: SET autocommit=0
@@ 导入 Data
@@ 恢复 Auto-commit: SET autocommit=1
Optimization of 6.INSERT statements
1. When inserting data, use INSERT INTO Tbl_name VALUES (' AA '), (' BB ') ... (' ZZ ');
7.GROUP by Statement optimization
1. Disable grouping sorting, using SELECT * from Tbl_name GROUP by Cloumn ORDER by NULL;
8. Nested optimization queries
1. Using nested queries, internally nested queries are indexed and not used in the outer layer.
To change a nested query to an inner or outer join, you can refine the query.
Two. Database optimization
1. Using the intermediate table
@@ 创建 a new table. #不够灵活
@@ 创建 a view. #推荐做法
2. Partitioning (optimization of massive data, available in Mysql5.1 and beyond)
# #MyISAM引擎:
@ @RANGE Type:
CREATE TABLE T1 (id int,name varchar (30))
-->partition by RANGE (ID) (
-->partition p0 VALUES less THAN (11),
-->partition p1 VALUES less THAN (21)
);
@ @LIST Type:
CREATE TABLE T1 (id int,name varchar (30))
-->partition by LIST (ID) (
-->partition p0 VALUES in (1,3,6,7,10),
-->partition p1 VALUES in (2,4,5,8,11)
);
@ @HASH Type:
CREATE TABLE T1 (id int,name varchar (30))
-->partition by HASH (ID)
-->partitions 2;
# #InnoDB引擎
@@ 修改 my.cnf
[Mysqld]
Innodb_file_per_table=1 #开启InnoDB的独立存储空间
@@ 其他 Other and MyISAM the same
Three. mysql Server optimization
# #锁机制
1.MyISAM Read lock
@@ 命令: LOCK TABLE tbl_name READ #所有用户只能读, cannot be updated, deleted, etc.
2.MyISAM Write lock
@@ 命令: LOCK TABLE tbl_name WRITE #只有当前用户可增删改查, no other user can do anything.
3. Unlock: UNLOCK TABLES;
# #字符集
[Email protected]@ use: status or \s to view basic information and character set.
The server character set, the database character set, the client character set, and the connection character set are available.
@@ 客户端 and connection character set settings
[Client]
Default-character-set=utf8
@@ 服务器 and database character set settings
[Mysqld]
Character-set-server=utf8
@@ 校验 Character Set
[Mysqld]
Collation-server=utf8_general_ci
Note: You can use show CHARACTER set; View the character set corresponding to the checksum character set.
# #开启慢查询日志
[Email protected]@ use: show VARIABLES like '%slow% '; see if the slow query log is turned on
@@ 开启: [mysqld]
Slow_query_log=slow.log
@@ 慢 Query time: [Mysqld]
Long_query_time=5
# #socket问题
1. If Mysql.sock is lost, you can use mysql-uroot-p--protocol tcp-h localhost
Note: The workaround is just a temporary startup.

2. Mysql password is missing
@@ 跳 Authorization form: Mysqld_safe--skip-grant-tables--user=mysql &

This article is from the "Everything Possible" blog, please be sure to keep this source http://noican.blog.51cto.com/4081966/1656574

MySQL Replication architecture and optimization