MySQL 學習筆記 （一）

1.InnoDB and Online DDL

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html

2.TRUNCATE TABLE后可用空間的使用

在innodb_file_per_table=on的條件下，可用空間釋放給了作業系統，而在innodb_file_per_table=OFF（system tablespace）或（ general tablespaces）情況下，空間可以從新利用，沒有物理釋放，

https://dev.mysql.com/doc/refman/8.0/en/innodb-truncate-table-reclaim-space.html

3.復制狀態查看

* 從庫查看slave_master_info表：select * from mysql.slave_master_info; 
* 從庫查看slave_relay_log_info表：select * from mysql.slave_relay_log_info; 
* 從庫查看slave_worker_info表：select * from mysql.slave_worker_info; 
* 從庫查看replication_applier_status_by_worker表：select * from performance_schema.replication_applier_status_by_worker; 
* 從庫查看replication_connection_status表：select * from performance_schema.replication_connection_status; 

 4.GTID Sets

來源于同一個Master Server的的GTID，可以構成一個集合：

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

The above example represents the first through fifth transactions originating on the MySQL server whose server_uuidis 3E11FA47-71CA-11E1-9E33-C80AA9429562. Multiple single GTIDs or ranges of GTIDs originating from the same server can also be included in a single expression, with the GTIDs or ranges separated by colons, as in the following example:

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-3:11:47-49

A GTID set can include any combination of single GTIDs and ranges of GTIDs, and it can include GTIDs originating from different servers. This example shows the GTID set stored in the gtid_executed system variable (@@GLOBAL.gtid_executed) of a slave that has applied transactions from more than one master:

2174B383-5441-11E8-B90A-C80AA9429562:1-3, 24DA167-0C0C-11E8-8442-00059A3C7B00:1-19

 5.gtid_executed table 

GTIDs are stored in the mysql.gtid_executed table only when gtid_mode is ON or ON_PERMISSIVE. Note that the mysql.gtid_executed table is cleared if you issue RESET MASTER.

Compression of the mysql.gtid_executed table is performed by a dedicated foreground thread namedthread/sql/compress_gtid_table.

SELECT * FROM performance_schema.threads WHERE NAME LIKE '%gtid%'\G

6.關于GTID復制模式的關聯報錯

If any of the transactions that should be sent by the master have been purged from the master's binary log, or added to the set of GTIDs in the gtid_purged system variable by another method, the master sends the errorER_MASTER_HAS_PURGED_REQUIRED_GTIDS to the slave, and replication does not start.  The GTIDs of the missing purged transactions are identified and listed in the master's error log in the warning message ER_FOUND_MISSING_GTIDS.

Attempting to reconnect without the MASTER_AUTO_POSITION option enabled only results in the loss of the purged transactions on the slave. The correct approach to recover from this situation is for the slave to replicate the missing transactions listed in the ER_FOUND_MISSING_GTIDS message from another source, or for the slave to be replaced by a new slave created from a more recent backup. Consider revising the binary log expiration period (binlog_expire_logs_seconds) on the master to ensure that the situation does not occur again.

If during the exchange of transactions it is found that the slave has received or committed transactions with the master's UUID in the GTID, but the master itself does not have a record of them, the master sends the errorER_SLAVE_HAS_MORE_GTIDS_THAN_MASTER to the slave and replication does not start. This situation can occur if a master that does not have sync_binlog=1 set experiences a power failure or operating system crash, and loses committed transactions that have not yet been synchronized to the binary log file, but have been received by the slave. 

https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-auto-positioning.html

7.復制的權限設定

Most of the steps that follow require the use of the MySQL root account or another MySQL user account that has theSUPER privilege. mysqladmin shutdown requires either the SUPER privilege or the SHUTDOWN privilege.

8.將MySQL 設定為read_only 

Make the servers read-only by setting the read_only system variable to ON on each server by issuing the following:

mysql> SET @@GLOBAL.read_only = ON;

這個命令的重要作用是：

Wait for all ongoing transactions to commit or roll back. Then, allow the slave to catch up with the master. It is extremely important that you make sure the slave has processed all updates before continuing.

9.shut down the MySQL

shell> mysqladmin -uusername -p shutdown

Then supply this user's password at the prompt.

https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-howto.html

https://www.cnblogs.com/dadonggg/p/8625500.html

10.如何跳過一個GTID

基于GTID的復制，跳過一個事務，需要利用一個空事務來完成，

stop slave;

SET GTID_NEXT='aaa-bbb-ccc-ddd:N';

BEGIN;
COMMIT;

SET GTID_NEXT='AUTOMATIC';

start slave;

https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-failover.html

11.多源復制

In a multi-source replication topology, a slave creates a replication channel for each master that it should receive transactions from.

The error codes and messages that are issued when multi-source replication is enabled specify the channel that generated the error.

https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source.html

 12.顯示創建表的scripts

show create table student;

13 shell 操作mysql

關于salve節點的重新執行SQL的執行緒

mysql -e 'STOP SLAVE SQL_THREAD;'

14.mysqldump

Run mysqldump to dump your databases. You may either dump all databases or select databases to be dumped. For example, to dump all databases:

mysqldump --all-databases > fulldb.dump

備份資料庫結構，不備份資料

格式：mysqldump -h主機名 -P埠 -u用戶名 -p密碼 --no-data 資料庫名1 資料庫名2 資料庫名3 > 檔案名.sql

mysqldump --no-data –databases db1 db2 cmdb > /data/backup/structure.sql

https://dev.mysql.com/doc/refman/8.0/en/replication-solutions-backups-mysqldump.html

https://baijiahao.baidu.com/s?id=1612955427840289665&wfr=spider&for=pc

15.基于既有表創建一個新表

• create table as 只是復制原資料，其實就是把查詢的結果建一個表
• create table like 產生與源表相同的表結構，包括索引和主鍵，資料需要用insert into 陳述句復制進去，例如：

create table newtest like test；
insert into newtest select * from test；

 16.MHA FailOver

MHA 在線切換程序
https://blog.csdn.net/leshami/article/details/45189825

MHA 手動故障轉移

 https://blog.csdn.net/leshami/article/details/45219821

17.GTID模式下配置主從

change master to master_host='172.XXX.XXX.XXX',master_port=????,master_user='XXXX',master_password='XXXXXX',master_auto_position=1;

start slave;

 18.手動啟動MHA Manager

nohup /usr/local/bin/masterha_manager --conf=/etc/mha/app1.cnf  --remove_dead_master_conf --ignore_last_failover < /dev/null > /data/log/mha/manager.log >&1 &

19.查看某資料庫下所有表的具體資訊（information_schema.TABLES）

 SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'XXXXdb';

例如查看資料庫中以winxin開頭的各表的資料量

 SELECT table_name,table_rows FROM information_schema.tables WHERE TABLE_name like 'winxin%' ORDER BY  table_rows DESC;

20.生成批量修改表的SQL陳述句

例如：生成清空分庫分表中的ABC開頭某類表

SELECT CONCAT( 'truncate table ', table_name, ';' ) 
FROM information_schema.tables
WHERE table_name LIKE 'ABC_%' and  table_name  not LIKE 'terminal_user_%' ;

如果還要加上庫名，例如洗掉某類表

SELECT CONCAT('drop table QQ_weixin_co.', table_name, ';') 
FROM information_schema.tables 
WHERE table_schema = 'QQ_weixin_co' AND table_name LIKE 'ABC_%'

21 Truncate 命令在 binlog中的記錄形式

TRUNCATE TABLE is treated for purposes of binary logging and replication as DROP TABLE followed by CREATE TABLE—that is, as DDL rather than DML. This is due to the fact that, when using InnoDB and other transactional storage engines where the transaction isolation level does not permit statement-based logging (READ COMMITTED or READ UNCOMMITTED), the statement was not logged and replicated when using STATEMENT or MIXED logging mode. (Bug #36763) However, it is still applied on replication slaves using InnoDB in the manner described previously.

--https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html

22.洗掉復制關系

RESET SLAVE makes the slave forget its replication position in the master's binary log. This statement is meant to be used for a clean start: It clears the master info and relay log info repositories, deletes all the relay log files, and starts a new relay log file. It also resets to 0 the replication delay specified with the MASTER_DELAY option to CHANGE MASTER TO.

To use RESET SLAVE, the slave replication threads must be stopped, so on a running slave use STOP SLAVE before issuing RESET SLAVE.

mysql主從復制中，需要將從庫提升為主庫，需要取消其從庫角色，這可通過執行RESET SLAVE ALL清除從庫的同步復制資訊、包括連接資訊和二進制檔案名、位置，從庫上執行這個命令后，使用show slave status將不會有輸出，

--個人學習筆記系列，可能比較粗糙，觀者見諒，

標籤：MySQL

上一篇：web專案踩坑程序

下一篇：web專案的初始搭建和intellij的tomcat的配置