Table Maintenance Statements

Replica Errors During Replication

If a statement produces the same error (identical error code) on both the source and the replica, the error is logged, but replication continues.
主从都出现相同的错误，同步继续

If a statement produces different errors on the source and the replica, the replication SQL thread terminates, and the replica writes a message to its error log and waits for the database administrator to decide what to do about the error. This includes the case that a statement produces an error on the source or the replica, but not both. To address the issue, connect to the replica manually and determine the cause of the problem. SHOW SLAVE STATUS is useful for this. Then fix the problem and run START SLAVE. For example, you might need to create a nonexistent table before you can start the replica again.

主从产生不同的错误，复制语句中断，写入错误日志并等待管理员操作，错误在主或者从产生，需要手动连接到备库并处理。 查看状态SHOW SLAVE STATUS；启动 START SLAVE

If a temporary error is recorded in the replica's error log, you do not necessarily have to take any action suggested in the quoted error message. Temporary errors should be handled by the client retrying the transaction. For example, if the replication SQL thread records a temporary error relating to a deadlock, you do not need to restart the transaction manually on the replica, unless the replication SQL thread subsequently terminates with a nontemporary error message.

临时错误不需要处理，例如死锁

If this error code validation behavior is not desirable, some or all errors can be masked out (ignored) with the --slave-skip-errors option.
如果不需要验证数据，可以使用--slave-skip-errors

For nontransactional storage engines such as MyISAM, it is possible to have a statement that only partially updates a table and returns an error code. This can happen, for example, on a multiple-row insert that has one row violating a key constraint, or if a long update statement is killed after updating some of the rows. If that happens on the source, the replica expects execution of the statement to result in the same error code. If it does not, the replication SQL thread stops as described previously.
非事务数据库中， 如果有sql导致错误，主从错误不一致，会停止同步进程。

If you are replicating between tables that use different storage engines on the source and replica, keep in mind that the same statement might produce a different error when run against one version of the table, but not the other, or might cause an error for one version of the table, but not the other. For example, since MyISAM ignores foreign key constraints, an INSERT or UPDATE statement accessing an InnoDB table on the source might cause a foreign key violation but the same statement performed on a MyISAM version of the same table on the replica would produce no such error, causing replication to stop.

主从表用不同的存储引擎，可能导致主从停止
Stored functions execute with the same NOW() value as the calling statement. However, this is not true of stored procedures.
存储函数now（） 调用相同的语句，但是存储过程不会。
Deterministic loadable functions must be applied on the replicas.
确定性函数

Table definitions must be (nearly) identical on source and replica. See Section 16.4.1.10, “Replication with Differing Table Definitions on Source and Replica”, for more information.
主从表要一样

Advantages of row-based replication
All changes can be replicated. This is the safest form of replication.
所有更改都能复制

Statements that update the information in the mysql system database, such as GRANT, REVOKE and the manipulation of triggers, stored routines (including stored procedures), and views, are all replicated to replicas using statement-based replication.

For statements such as CREATE TABLE ... SELECT, a CREATE statement is generated from the table definition and replicated using statement-based format, while the row insertions are replicated using row-based format.
DDL 主要从语句复制、DML主要是行复制
Fewer row locks are required on the source, which thus achieves higher concurrency, for the following types of statements:
以下语句实现高并发产生更少的行锁
INSERT ... SELECT

INSERT statements with AUTO_INCREMENT

UPDATE or DELETE statements with WHERE clauses that do not use keys or do not change most of the examined rows.
Fewer row locks are required on the replica for any INSERT, UPDATE, or DELETE statement.

Disadvantages of row-based replication
行复制的缺点
RBR can generate more data that must be logged. To replicate a DML statement (such as an UPDATE or DELETE statement), statement-based replication writes only the statement to the binary log. By contrast, row-based replication writes each changed row to the binary log. If the statement changes many rows, row-based replication may write significantly more data to the binary log; this is true even for statements that are rolled back. This also means that making and restoring a backup can require more time. In addition, the binary log is locked for a longer time to write the data, which may cause concurrency problems. Use binlog_row_image=minimal to reduce the disadvantage considerably.
大量数据操作的DML语句会产生更多的BINLOG，备份和恢复需要更多的时间,锁定BINLOG的时间更长，无法写入数据，可能影响并发性能，binlog_row_image=minimal 可以大大减少缺点。但是生产系统上不建议使用，原因有2，一是大表更新过程中取消会有问题，闪回等功能无法使用。因为该参数省略了修改前结果的保存。

原理：

Replication is based on the replication source server keeping track of all changes to its databases (updates, deletes, and so on) in its binary log. The binary log serves as a written record of all events that modify database structure or content (data) from the moment the server was started. Typically, SELECT statements are not recorded because they modify neither database structure nor content.
复制是基于源库BINLOG中记录的所有改变。

Each replica that connects to the source requests a copy of the binary log. That is, it pulls the data from the source, rather than the source pushing the data to the replica. The replica also executes the events from the binary log that it receives. This has the effect of repeating the original changes just as they were made on the source. Tables are created or their structure modified, and data is inserted, deleted, and updated according to the changes that were originally made on the source.
每个恢复连接进程请求一份源的BINARY LOG复制，这是备从源拉取，而不是源主动推送的，备考重做源库的所有操作。

Because each replica is independent, the replaying of the changes from the source's binary log occurs independently on each replica that is connected to the source. In addition, because each replica receives a copy of the binary log only by requesting it from the source, the replica is able to read and update the copy of the database at its own pace and can start and stop the replication process at will without affecting the ability to update to the latest database status on either the source or replica side.
由于恢复的I/O进程是从库主动发起的，主库和从库是独立的，主从不会影响主库和从库的正常运行。

复制方式（格式）

Replication works because events written to the binary log are read from the source and then processed on the replica. The events are recorded within the binary log in different formats according to the type of event. The different replication formats used correspond to the binary logging format used when the events were recorded in the source's binary log. The correlation between binary logging formats and the terms used during replication are:
一般情况用混合复制，特殊情况，可以根据需要指定基于行的或者语句的复制方式。
Advantages of statement-based replication
Disadvantages of statement-based replication

语句复制的优点

Proven technology.
成熟的技术
Less data written to log files. When updates or deletes affect many rows, this results in much less storage space required for log files. This also means that taking and restoring from backups can be accomplished more quickly.
当更新、删除多个行时，需要更少的空间写入日志文件，同时意味着更高效的备份和恢复
Log files contain all statements that made any changes, so they can be used to audit the database.
可以用于数据库审计

语句复制的缺点

Statements that are unsafe for SBR. Not all statements which modify data (such as INSERT DELETE, UPDATE, and REPLACE statements) can be replicated using statement-based replication. Any nondeterministic behavior is difficult to replicate when using statement-based replication. Examples of such Data Modification Language (DML) statements include the following:
不支持全部的sql语句
A statement that depends on a loadable function or stored program that is nondeterministic, since the value returned by such a function or stored program or depends on factors other than the parameters supplied to it. (Row-based replication, however, simply replicates the value returned by the function or stored program, so its effect on table rows and data is the same on both the source and replica.) See Section 16.4.1.16, “Replication of Invoked Features”, for more information.
例如函数和存储过程
DELETE and UPDATE statements that use a LIMIT clause without an ORDER BY are nondeterministic. See Section 16.4.1.17, “Replication and LIMIT”.
使用limit但未包含order by的语句
Deterministic loadable functions must be applied on the replicas.
复制到备库的确定性的函数
不支持的函数：
LOAD_FILE()

UUID(), UUID_SHORT()

USER()

FOUND_ROWS()

SYSDATE() (unless both the source and the replica are started with the --sysdate-is-now option)

GET_LOCK()

IS_FREE_LOCK()

IS_USED_LOCK()

MASTER_POS_WAIT()

RAND()

RELEASE_LOCK()

SLEEP()

VERSION()
语句使用基于sql的复制，失败的报错如下；

[Warning] Statement is not safe to log in statement format.

INSERT ... SELECT requires a greater number of row-level locks than with row-based replication.
INSERT ... SELECT 比基于行的复制产生更多的行级锁

UPDATE statements that require a table scan (because no index is used in the WHERE clause) must lock a greater number of rows than with row-based replication.
没有索引的update 语句会产生大量行锁
For InnoDB: An INSERT statement that uses AUTO_INCREMENT blocks other nonconflicting INSERT statements.
innodb中，自增的insert会阻塞其他的insert

For complex statements, the statement must be evaluated and executed on the replica before the rows are updated or inserted. With row-based replication, the replica only has to modify the affected rows, not execute the full statement.
对于复杂语句，需要影响更多的行，而基于行的复制，只影响被修改的行。

Advantages of row-based replication
Disadvantages of row-based replication
行复制的缺点

https://www.processon.com/view/link/60e4f6d65653bb46e90ece0b
参考：https://dev.mysql.com/doc/refman/5.7/en/replication-implementation.html

1.查看所有数据库容量大小

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;
　　

2.查看所有数据库各表容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;
　　

3.查看指定数据库容量大小
例：查看mysql库容量大小

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';
　　

4.查看指定数据库各表容量大小
例：查看mysql库各表容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

mysql压测工具测试
一、mysql自带压测工具mysqlslap
1、更改其默认的最大连接数
vi /etc/my.cnf
max_connections=2000
2、查看最大连接数
show variables like '%max_connects%';
3、进行压力测试
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100,200 \ ---模拟两次读写并发，第一次100，第二次200
--iterations=20 \ ---模拟查询20秒
--number-int-cols=20 \ ---测试表包含20个init字段
--number-char-cols=30 \ ---测试表包含30个char字段
--auto-generate-sql \ ---自动生成SQL脚本
--auto-generate-sql-add-autoincrement \ ---自动添加一个自动增长的列到测试表
--auto-generate-sql-load-type=mixed \ ---指定测试加载类型
--engine=myisam,innodb \ ---测试引擎分别为myisam、innodb
--number-of-queries=2000 \ ---每次执行2000个查询请求
--query=query.sql ---指定模拟查询用sql文件
--create=create.sql ---指定模拟创建用sql文件
-uroot -pcqfae0630 \ ---用户名、密码
--verbose ---详细模式，打印有关详细信息
4、测试结果
Benchmark

Running for engine myisam
Average number of seconds to run all queries: 1.338 seconds
Minimum number of seconds to run all queries: 1.338 seconds
Maximum number of seconds to run all queries: 1.338 seconds
Number of clients running queries: 100
Average number of queries per client: 20

Benchmark

Running for engine myisam
Average number of seconds to run all queries: 1.203 seconds
Minimum number of seconds to run all queries: 1.203 seconds
Maximum number of seconds to run all queries: 1.203 seconds
Number of clients running queries: 200
Average number of queries per client: 10

Benchmark

Running for engine innodb
Average number of seconds to run all queries: 0.134 seconds
Minimum number of seconds to run all queries: 0.134 seconds
Maximum number of seconds to run all queries: 0.134 seconds
Number of clients running queries: 100
Average number of queries per client: 20

Benchmark

Running for engine innodb
Average number of seconds to run all queries: 0.142 seconds
Minimum number of seconds to run all queries: 0.142 seconds
Maximum number of seconds to run all queries: 0.142 seconds
Number of clients running queries: 200
Average number of queries per client: 10

二、第三方压测工具sysbench