My Certification

Oracle Database 10g OCP
Oracle Database 11g OCP
Oracle Database 12c OCP
Oracle GoldenGate 10 OCS
Oracle 11gR2 RAC OCE
MySQL 5.6 Database OCP
Novell SUSE Linux 10 CLA
IBM Power System AIX 7.1



MySQL Replication参数详解

在MySQL中和复制相关的参数主要有以下几种
–replicate-do-db
–replicate-ignore-db
–replicate-do-table
–replicate-wild-do-table
–replicate-ignore-table
–replicate-wild-ignore-table


–replicate-do-db
The effects of this option depend on whether statement-based or row-based replication is in use.
Statement-based replication.  Tell the slave SQL thread to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database; however, doing so does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while a different database (or no database) is selected.

一般只同步A,B,C库,大部分人会在配置文件中这样写,如果真这样写了那么恭喜,悲剧发生了
–replicate-do-db=A,B,C
官方解释如下
Warning
To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.

该参数会将A,B,C看作为1个数据库,其实问题还不止这一个,结合实际情况复制是分为database和table两种级别。

 

Database-Level 
这个级别比较容易理解,其规则只针对binlog_format=STATEMENT or MIXED有效;如果binlog_format=ROW那么不受库级别规则限制只受表级别规则限制。

Database-Level Replication
参考mysql5.6官方文档
https://dev.mysql.com/doc/refman/5.6/en/replication-rules-db-options.html
Note
Only DML statements can be logged using the row format. DDL statements are always logged as statements, even when binlog_format=ROW. All DDL statements are therefore always filtered according to the rules for statement-based replication. This means that you must select the default database explicitly with a USE statement in order for a DDL statement to be applied.

 


Table-Level

Table-Level Replication
参考mysql5.6官方文档
https://dev.mysql.com/doc/refman/5.6/en/replication-rules-table-options.html
Note
For statement-based replication, replication events represent statements (all changes making up a given event are associated with a single SQL statement); for row-based replication, each event represents a change in a single table row (thus a single statement such as UPDATE mytable SET mycol = 1 may yield many row-based events). When viewed in terms of events, the process of checking table options is the same for both row-based and statement-based replication.
Statement-based replication stops if a single SQL statement operates on both a table that is included by a –replicate-do-table or –replicate-wild-do-table option, and another table that is ignored by a –replicate-ignore-table or –replicate-wild-ignore-table option. The slave must either execute or ignore the complete statement (which forms a replication event), and it cannot logically do this. This also applies to row-based replication for DDL statements, because DDL statements are always logged as statements, without regard to the logging format in effect. The only type of statement that can update both an included and an ignored table and still be replicated successfully is a DML statement that has been logged with binlog_format=ROW.

 


我这里遇到的场景
将master上的A,B,C库复制到slave库,但是slave端不复制mysql库
replicate-ignore-db=mysql
replicate-wild-ignore-table=mysql.%
replicate_do_DB=A
replicate_do_DB=B
replicate_do_DB=C
replicate-wild-do-table=A.%
replicate-wild-do-table=B.%
replicate-wild-do-table=C.%
以上参数配置存在一个误区:
如果default database不是A,B或者C库,那么接下来的操作就不会被slave执行然后你就悲剧了
master==>use A;insert into C.TAB values(1);
所以以上的配置只适合default database是A,B或者C的情况。
如果要完成这种需求可以参考以下操作(前提条件是应用没有权限访问到mysql库)
replicate-ignore-db=mysql
replicate-wild-ignore-table=mysql.%
replicate-wild-do-table=A.%
replicate-wild-do-table=B.%
replicate-wild-do-table=C.%

 

其余复制参数描述可以参考官方文档
https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html
–replicate-ignore-db
–replicate-do-table
–replicate-wild-do-table
–replicate-ignore-table
–replicate-wild-ignore-table

How to calculate a good InnoDB log file size

innodb_log_file_size设置过小会加快checkpoint降低性能,由于是innodb log是顺序IO设置过大对性能有所提高但是遇到以外宕机等情况再恢复过程中可能会需要太久的时间。
可以参考以下的方法来设置该值,计算出每小时产生的日志量,再配合innodb_log_files_in_group的值

mysql> pager grep sequence
PAGER set to 'grep sequence'
mysql> 
mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 510190633330
1 row in set (0.01 sec)

1 row in set (1 min 0.00 sec)

Log sequence number 510191524314
1 row in set (0.02 sec)

mysql> nopager
PAGER set to stdout

mysql> select ((510191524314 – 510190633330) / 1024 / 1024) * 60 as MB_per_hour;
+————-+
| MB_per_hour |
+————-+
| 50.98251343 |
+————-+
1 row in set (0.00 sec)


当前环境innodb_log_files_in_group是2组,所以innodb_log_file_size值可以设置为50/2=25M
mysql> show variables like 'innodb_log_files_in_group';
+—————————+——-+
| Variable_name             | Value |
+—————————+——-+
| innodb_log_files_in_group | 2     |
+—————————+——-+

MySQL and Clickhouse Data Types

MySQL Data Types

Numeric Types

  • BIT the number of bits per value, from 1 to 64

  • TINYINT -128 to 127. The unsigned range is 0 to 255

  • BOOLBOOLEAN synonyms for TINYINT(1)

  • SMALLINT -32768 to 32767. The unsigned range is 0 to 65535

  • MEDIUMINT -8388608 to 8388607. The unsigned range is 0 to 16777215.

  • INTINTEGER -2147483648 to 2147483647. The unsigned range is 0 to 4294967295

  • BIGINT -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615

  • SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

  • DECDECIMALFIXEDNUMERIC A packed ?exact? fixed-point number

  • FLOAT Permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38

  • DOUBLEREAL Permissible values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308

Date and Time Types

  • DATE The supported range is '1000-01-01' to '9999-12-31'
  • DATETIME The supported range is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'
  • TIMESTAMP The range is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999'
  • TIME The range is '-838:59:59.000000' to '838:59:59.000000'
  • YEAR Values display as 1901 to 2155, and 0000

String Types

  • CHAR The range of M is 0 to 255. If M is omitted, the length is 1.

  • VARCHAR The range of M is 0 to 65,535

  • BINARY similar to CHAR

  • VARBINARY similar to VARCHAR

  • TINYBLOB maximum length of 255

  • TINYTEXT maximum length of 255

  • BLOB maximum length of 65,535

  • TEXT maximum length of 65,535

  • MEDIUMBLOB maximum length of 16,777,215

  • MEDIUMTEXT maximum length of 16,777,215

  • LONGBLOB maximum length of 4,294,967,295 or 4GB

  • LONGTEXT maximum length of 4,294,967,295 or 4GB

  • ENUM can have a maximum of 65,535 distinct elements

  • SET can have a maximum of 64 distinct members

  • JSON native JSON data type defined by RFC 7159


ClickHouse Data Types

  • Date number of days since 1970-01-01

  • DateTime Unix timestamp

  • Enum8 or Enum16. A set of enumerated string values that are stored as Int8 or Int16. The numeric values must be within -128..127 for Enum8 and -32768..32767 for Enum16

  • Float32Float64

  • Int8 -128 127

  • UInt8 0 255

  • Int16 -32768 32767

  • UInt16 0 65535

  • Int32 -2147483648 2147483647

  • UInt32 0 4294967295

  • Int64 -9223372036854775808 9223372036854775807

  • UInt64 0 18446744073709551615

  • FixedString(N) string of N bytes (not characters or code points)

  • String The length is not limited. The value can contain an arbitrary set of bytes, including null bytes


MySQL -> ClickHouse Data Types Mapping

Numeric Types

  • BIT -> ??? (possibly String?)

  • TINYINT -> Int8UInt8

  • BOOLBOOLEAN -> UInt8

  • SMALLINT -> Int16UInt16

  • MEDIUMINT -> Int32UInt32

  • INTINTEGER -> Int32UInt32

  • BIGINT -> Int64UInt64

  • SERIAL -> UInt64

  • DECDECIMALFIXEDNUMERIC -> ???? (possibly String?)

  • FLOAT -> Float32

  • DOUBLEREAL -> Float64

Date and Time Types

  • DATE -> Date (for valid values) or String Date Allows storing values from just after the beginning of the Unix Epoch to the upper threshold defined by a constant at the compilation stage (currently, this is until the year 2038, but it may be expanded to 2106)
  • DATETIME -> DateTime (for valid values) or String
  • TIMESTAMP -> DateTime
  • TIME -> ????? (possibly String?)
  • YEAR -> UInt16

String Types

  • CHAR -> FixedString
  • VARCHAR -> String
  • BINARY -> String
  • VARBINARY -> String
  • TINYBLOB -> String
  • TINYTEXT -> String
  • BLOB -> String
  • TEXT -> String
  • MEDIUMBLOB -> String
  • MEDIUMTEXT -> String
  • LONGBLOB -> String
  • LONGTEXT -> String

Set Types

  • ENUM -> Enum8Enum16
  • SET -> Array(Int8)

Custom Types

  • JSON -> ?????? (possibly String?)

 

 

 

MySQL の型 ClickHouse の型 備考
tinyint UInt8 / Int8  
smallint UInt16 / Int16  
int / mediumint UInt32 / Int32  
bigint UInt64 / Int64  
float Float32  
double Float64  
date Date 表現できる範囲差あり
datetime DateTime 表現できる範囲差あり
timestamp DateTime  
binary FixedString  
上記以外 String  

Python Installation on IBM AIX 7.1/6.1

Install
tar xvf python-2.6.8-1_AIX61_RPM_pkgs-20160718.tar
x python-2.6.8
x python-2.6.8/bash-4.3-13.aix5.1.ppc.rpm, 2098335 bytes, 4099 media blocks.
x python-2.6.8/bzip2-1.0.6-1.aix5.1.ppc.rpm, 122731 bytes, 240 media blocks.
x python-2.6.8/db4-4.7.25-2.aix5.1.ppc.rpm, 3054640 bytes, 5967 media blocks.
x python-2.6.8/expat-2.1.0-1.aix5.1.ppc.rpm, 389811 bytes, 762 media blocks.
x python-2.6.8/fontconfig-2.10.2-1.aix5.1.ppc.rpm, 1016700 bytes, 1986 media blocks.
x python-2.6.8/freetype2-2.5.3-1.aix5.1.ppc.rpm, 763223 bytes, 1491 media blocks.
x python-2.6.8/gdbm-1.11-1.aix5.1.ppc.rpm, 102301 bytes, 200 media blocks.
x python-2.6.8/gettext-0.10.40-8.aix5.2.ppc.rpm, 1074719 bytes, 2100 media blocks.
x python-2.6.8/gmp-6.0.0a-1.aix5.1.ppc.rpm, 1529950 bytes, 2989 media blocks.
x python-2.6.8/info-5.1-2.aix5.1.ppc.rpm, 262713 bytes, 514 media blocks.
x python-2.6.8/libXft-2.3.1-1.aix5.1.ppc.rpm, 217394 bytes, 425 media blocks.
x python-2.6.8/libXrender-0.9.8-1.aix6.1.ppc.rpm, 139468 bytes, 273 media blocks.
x python-2.6.8/libdbi-0.8.4-1.aix5.1.ppc.rpm, 123683 bytes, 242 media blocks.
x python-2.6.8/libffi-3.2.1-1.aix5.1.ppc.rpm, 131626 bytes, 258 media blocks.
x python-2.6.8/libgcc-4.8.3-1.aix7.1.ppc.rpm, 926039 bytes, 1809 media blocks.
x python-2.6.8/libiconv-1.14-2.aix5.1.ppc.rpm, 1582858 bytes, 3092 media blocks.
x python-2.6.8/libpng-1.6.16-1.aix5.1.ppc.rpm, 1290852 bytes, 2522 media blocks.
x python-2.6.8/libstdc++-4.8.3-1.aix7.1.ppc.rpm, 10119514 bytes, 19765 media blocks.
x python-2.6.8/openssl-1.0.1l-1.aix5.1.ppc.rpm, 18204359 bytes, 35556 media blocks.
x python-2.6.8/python-2.6.8-2.6.8-1.aix6.1.ppc.rpm, 14121725 bytes, 27582 media blocks.
x python-2.6.8/python-2.6.8-libs-2.6.8-1.aix6.1.ppc.rpm, 1786610 bytes, 3490 media blocks.
x python-2.6.8/readline-6.3-5.aix5.1.ppc.rpm, 840022 bytes, 1641 media blocks.
x python-2.6.8/sqlite-3.8.7.1-1.aix5.1.ppc.rpm, 734476 bytes, 1435 media blocks.
x python-2.6.8/tcl-8.6.3-1.aix5.1.ppc.rpm, 3169586 bytes, 6191 media blocks.
x python-2.6.8/tk-8.6.3-1.aix5.1.ppc.rpm, 2549937 bytes, 4981 media blocks.

 

Install the packges

# cd python-2.6.8
# rpm -Uvh *

bash                        ##################################################
bzip2                       ##################################################
db4                         ##################################################
expat                       ##################################################
fontconfig                  ##################################################
freetype2                   ##################################################
gdbm                        ##################################################
gettext                     ##################################################
gmp                         ##################################################
warning: /opt/freeware/info/dir created as /opt/freeware/info/dir.rpmnew
info                        ##################################################
Please check that /etc/info-dir does exist.
You might have to rename it from /etc/info-dir.rpmsave to /etc/info-dir.
libXft                      ##################################################
libXrender                  ##################################################
libdbi                      ##################################################
libffi                      ##################################################
libgcc                      ##################################################
libiconv                    ##################################################
libpng                      ##################################################
cannot remove /usr/lib – directory not empty
cannot remove /usr/bin – directory not empty
libstdc++                   ##################################################
warning: /var/ssl/openssl.cnf saved as /var/ssl/openssl.cnf.rpmorig
openssl                     ##################################################
python                      ##################################################
python-libs                 ##################################################
readline                    ##################################################
sqlite                      ##################################################
tcl                         ##################################################
tk                          ##################################################

 

Test  the python

# python
Python 2.6.8 (unknown, Jun  14 2018, 22:08:53) [C] on aix6
Type "help", "copyright", "credits" or "license" for more information.
>>> import platform
>>> print platform.system()
AIX
>>> quit()

使用mysqlserverclone快速克隆mysql实例

[root@aspros-mysql-web /root]# mysqlserverclone –help
MySQL Utilities mysqlserverclone version 1.6.5 
License type: Commercial
Usage: mysqlserverclone –server=user:pass@host:port:socket –new-data=/tmp/data2 –new-port=3310 –new-id=12 –root-password=root
 
mysqlserverclone – start another instance of a running server
 
Options:
  –version             show program's version number and exit
  –help                display a help message and exit
  –license             display program's license and exit
  –server=SERVER       connection information for the server in the form:
                        <user>[:]@[:][:] or
                        <login-path>[:][:] or <config-
                        path>[].
  –ssl-ca=SSL_CA       path to a file that contains a list of trusted SSL
                        CAs.
  –ssl-cert=SSL_CERT   name of the SSL certificate file to use for
                        establishing a secure connection.
  –ssl-key=SSL_KEY     name of the SSL key file to use for establishing a
                        secure connection.
  –ssl=SSL             specifies if the server connection requires use of
                        SSL. If an encrypted connection cannot be established,
                        the connection attempt fails. By default 0 (SSL not
                        required).
  –new-data=NEW_DATA   the full path to the location of the data directory
                        for the new instance. The path size must be smaller or
                        equal than 200 characters.
  –new-port=NEW_PORT   the new port for the new instance – default=3307
  –new-id=NEW_ID       the server_id for the new instance – default=2
  –root-password=ROOT_PASS
                        password for the root user
  –mysqld=MYSQLD       additional options for mysqld
  -w CMD_FILE, –write-command=CMD_FILE
                        path to file for writing startup command. For example:
                        start_server1.sh
  -v, –verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
  -q, –quiet           turn off all messages for quiet execution.
  –basedir=BASEDIR     the base directory for the server
  –delete-data         delete the folder specified by –new-data if it exists
                        and is not empty.
  –user=USER           user account to launch cloned server. Default is
                        current user.
  –start-timeout=START_TIMEOUT
                        Number of seconds to wait for server to start. Default
                        = 10.
  –force               Ignore the maximum path length and the low space
                        checks for the –new-data option.
 
 
 
 
[root@aspros-mysql-web /root]# mysqlserverclone –server=root:root@127.0.0.1:5639:/data/mysql/mysql5639/log/5639.sock \
> –new-data=/data/clonedb –new-port=3307 \
> –root-password=root –mysqld=–log-bin=bin-3307 \
> –new-id=7 –root-password=root \
> –user=mysql -vvv
WARNING: Using a password on the command line interface can be insecure.
# Cloning the MySQL server running on 127.0.0.1.
# Configuring new instance…
# Locating mysql tools…
# Location of files:
#                       mysqld: /service/mysql5639/bin/mysqld
#                   mysqladmin: /service/mysql5639/bin/mysqladmin
#      mysql_system_tables.sql: /service/mysql5639/share/mysql_system_tables.sql
# mysql_system_tables_data.sql: /service/mysql5639/share/mysql_system_tables_data.sql
# mysql_test_data_timezone.sql: /service/mysql5639/share/mysql_test_data_timezone.sql
#         fill_help_tables.sql: /service/mysql5639/share/fill_help_tables.sql
# Setting up empty database and mysql tables…
2018-06-12 14:34:08 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2018-06-12 14:34:08 0 [Note] Ignoring –secure-file-priv value as server is running with –bootstrap.
2018-06-12 14:34:08 0 [Note] /service/mysql5639/bin/mysqld (mysqld 5.6.39) starting as process 109961 …
2018-06-12 14:34:08 109961 [Note] Plugin 'FEDERATED' is disabled.
2018-06-12 14:34:08 109961 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-06-12 14:34:08 109961 [Note] InnoDB: The InnoDB memory heap is disabled
2018-06-12 14:34:08 109961 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-06-12 14:34:08 109961 [Note] InnoDB: Memory barrier is not used
2018-06-12 14:34:08 109961 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-06-12 14:34:08 109961 [Note] InnoDB: Using CPU crc32 instructions
2018-06-12 14:34:08 109961 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-06-12 14:34:08 109961 [Note] InnoDB: Completed initialization of buffer pool
2018-06-12 14:34:08 109961 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2018-06-12 14:34:08 109961 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2018-06-12 14:34:08 109961 [Note] InnoDB: Database physically writes the file full: wait…
2018-06-12 14:34:08 109961 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2018-06-12 14:34:08 109961 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2018-06-12 14:34:08 109961 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2018-06-12 14:34:08 109961 [Warning] InnoDB: New log files created, LSN=45781
2018-06-12 14:34:08 109961 [Note] InnoDB: Doublewrite buffer not found: creating new
2018-06-12 14:34:08 109961 [Note] InnoDB: Doublewrite buffer created
2018-06-12 14:34:08 109961 [Note] InnoDB: 128 rollback segment(s) are active.
2018-06-12 14:34:08 109961 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-06-12 14:34:08 109961 [Note] InnoDB: Foreign key constraint system tables created
2018-06-12 14:34:08 109961 [Note] InnoDB: Creating tablespace and datafile system tables.
2018-06-12 14:34:08 109961 [Note] InnoDB: Tablespace and datafile system tables created.
2018-06-12 14:34:08 109961 [Note] InnoDB: Waiting for purge to start
2018-06-12 14:34:08 109961 [Note] InnoDB: 5.6.39 started; log sequence number 0
2018-06-12 14:34:08 109961 [Note] Binlog end
2018-06-12 14:34:08 109961 [Note] InnoDB: FTS optimize thread exiting.
2018-06-12 14:34:08 109961 [Note] InnoDB: Starting shutdown…
2018-06-12 14:34:10 109961 [Note] InnoDB: Shutdown completed; log sequence number 1625977
# Starting new instance of the server…
# Startup command for new server:
/service/mysql5639/bin/mysqld –no-defaults –log-bin=bin-3307 –basedir=/service/mysql5639 –datadir=/data/clonedb –pid-file=/data/clonedb/clone.pid –port=3307 –server-id=7 –socket=/data/clonedb/mysql.sock –tmpdir=/data/clonedb –user=mysql
# Testing connection to new instance…
2018-06-12 14:34:10 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2018-06-12 14:34:10 0 [Note] –secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2018-06-12 14:34:10 0 [Note] /service/mysql5639/bin/mysqld (mysqld 5.6.39-log) starting as process 109985 …
2018-06-12 14:34:10 109985 [Note] Plugin 'FEDERATED' is disabled.
2018-06-12 14:34:10 109985 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-06-12 14:34:10 109985 [Note] InnoDB: The InnoDB memory heap is disabled
2018-06-12 14:34:10 109985 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-06-12 14:34:10 109985 [Note] InnoDB: Memory barrier is not used
2018-06-12 14:34:10 109985 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-06-12 14:34:10 109985 [Note] InnoDB: Using CPU crc32 instructions
2018-06-12 14:34:10 109985 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-06-12 14:34:10 109985 [Note] InnoDB: Completed initialization of buffer pool
2018-06-12 14:34:10 109985 [Note] InnoDB: Highest supported file format is Barracuda.
2018-06-12 14:34:10 109985 [Note] InnoDB: 128 rollback segment(s) are active.
2018-06-12 14:34:10 109985 [Note] InnoDB: Waiting for purge to start
2018-06-12 14:34:10 109985 [Note] InnoDB: 5.6.39 started; log sequence number 1625977
2018-06-12 14:34:10 109985 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9d5884e8-6e0a-11e8-ab15-005056975182.
2018-06-12 14:34:10 109985 [Note] Server hostname (bind-address): '*'; port: 3307
2018-06-12 14:34:10 109985 [Note] IPv6 is available.
2018-06-12 14:34:10 109985 [Note]   - '::' resolves to '::';
2018-06-12 14:34:10 109985 [Note] Server socket created on IP: '::'.
2018-06-12 14:34:10 109985 [Note] Event Scheduler: Loaded 0 events
2018-06-12 14:34:10 109985 [Note] /service/mysql5639/bin/mysqld: ready for connections.
Version: '5.6.39-log'  socket: '/data/clonedb/mysql.sock'  port: 3307  Aspros-MySQL
# trying again…
# Success!
# Setting the root password…
Warning: Using a password on the command line interface can be insecure.
# Connection Information:
#  -uroot -proot –socket=/data/clonedb/mysql.sock
#…done.
 

MySQL如何处理唯一索引中的NULL值

在MySQL中,NULL值与其他值的处理方式不同,NULL值具体信息如下
mysql5.6–>root@zwc>select now();
+———————+
| now()               |
+———————+
| 2018-06-02 16:55:50 |
+———————+
1 row in set (0.00 sec)

mysql5.6–>root@zwc>SELECT 1 IS NULL, 1 IS NOT NULL;
+———–+—————+
| 1 IS NULL | 1 IS NOT NULL |
+———–+—————+
|         0 |             1 |
+———–+—————+
1 row in set (0.00 sec)

mysql5.6–>root@zwc>SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+———-+———–+———-+———-+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+———-+———–+———-+———-+
|     NULL |      NULL |     NULL |     NULL |
+———-+———–+———-+———-+
1 row in set (0.00 sec)

mysql5.6–>root@zwc>SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+———–+—————+————+—————-+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+———–+—————+————+—————-+
|         0 |             1 |          0 |              1 |
+———–+—————+————+—————-+
1 row in set (0.00 sec)

 


在MySQL里如果唯一索引中的一个或多个列为空,则允许唯一索引有两个相同的值
mysql5.6–>root@zwc>CREATE TABLE t_uk_test (
    -> a int unsigned DEFAULT NULL,
    -> b int unsigned DEFAULT NULL,
    -> UNIQUE INDEX (a, b)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;;
Query OK, 0 rows affected (0.05 sec)

mysql5.6–>root@zwc>INSERT INTO t_uk_test VALUES (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql5.6–>root@zwc>INSERT INTO t_uk_test VALUES (1, 1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'a'
mysql5.6–>root@zwc>

mysql5.6–>root@zwc>INSERT INTO t_uk_test VALUES (1, NULL);
Query OK, 1 row affected (0.00 sec)

mysql5.6–>root@zwc>INSERT INTO t_uk_test VALUES (1, NULL);
Query OK, 1 row affected (0.01 sec)

mysql5.6–>root@zwc>select * from t_uk_test;
+——+——+
| a    | b    |
+——+——+

|    1 | NULL |
|    1 | NULL |

|    1 |    1 |
+——+——+

 

那么在这种情况下如何确保唯一索引的空值不相同
可以通过trigger的方式来避免,在生产环境中需要考虑到error code,error message

DELIMITER //
CREATE TRIGGER tri_t_uk_test BEFORE INSERT ON t_uk_test FOR EACH ROW
BEGIN
   IF EXISTS(SELECT 1 FROM t_uk_test WHERE a <=> NEW.a AND b <=> NEW.b) THEN
      SIGNAL SQLSTATE '23000'
         SET MYSQL_ERRNO = 1062,
             TABLE_NAME = 't_uk_test',
             MESSAGE_TEXT = 'Duplicate key found';
   END IF;
END;
//
DELIMITER ;

 

删除t_uk_test表再次测试,发现trigger已经完全避免了该问题
mysql5.6–>root@zwc>drop table t_uk_test;
Query OK, 0 rows affected (0.00 sec)

mysql5.6–>root@zwc>CREATE TABLE `t_uk_test` (
    ->   `a` int(10) unsigned DEFAULT NULL,
    ->   `b` int(10) unsigned DEFAULT NULL,
    ->   UNIQUE KEY `a` (`a`,`b`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql5.6–>root@zwc>DELIMITER //
mysql5.6–>root@zwc>CREATE TRIGGER tri_t_uk_test BEFORE INSERT ON t_uk_test FOR EACH ROW
    -> BEGIN
    ->    IF EXISTS(SELECT 1 FROM t_uk_test WHERE a <=> NEW.a AND b <=> NEW.b) THEN
    ->       SIGNAL SQLSTATE '23000'
    ->          SET MYSQL_ERRNO = 1062,
    ->              TABLE_NAME = 't_uk_test',
    ->              MESSAGE_TEXT = 'Duplicate key found';
    ->    END IF;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql5.6–>root@zwc>DELIMITER ;
mysql5.6–>root@zwc>
mysql5.6–>root@zwc>INSERT INTO t_uk_test VALUES (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql5.6–>root@zwc>INSERT INTO t_uk_test VALUES (1, NULL);
Query OK, 1 row affected (0.00 sec)

mysql5.6–>root@zwc>INSERT INTO t_uk_test VALUES (1, NULL);
ERROR 1062 (23000): Duplicate key found

Oracle Tablespace Size SQL scripts

SELECT a.tablespace_name,
       total,
       free,
       (total – free) "used",
       total / (1024 * 1024 * 1024) "total_GB",
       free / (1024 * 1024 * 1024) "free_GB",
       (total – free) / (1024 * 1024 * 1024) "used_GB",
       round((total – free) / total, 4) * 100 "usage rate%"
  FROM (SELECT tablespace_name, SUM(bytes) free
          FROM dba_free_space
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total
          FROM dba_data_files
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name
 order by 5, 1 desc;

MySQL查看未提交的事务

在MySQL5.5包括之后的版本可以通过查询information_schema.INNODB_TRX数据字典得知
mysql> SELECT trx_id, trx_mysql_thread_id, trx_state, trx_started, trx_isolation_level, trx_query FROM information_schema.INNODB_TRX;
+——–+———————+———–+———————+———————+———————————————————————————————-+
| trx_id | trx_mysql_thread_id | trx_state | trx_started         | trx_isolation_level | trx_query                                                                                    |
+——–+———————+———–+———————+———————+———————————————————————————————-+
|   7000 |                  46 | RUNNING   | 2018-05-17 16:23:14 | REPEATABLE READ     | LOAD DATA LOCAL INFILE 'demo.txt' INTO TABLE 'demo' CHARACTER SET utf8                       |
|   6689 |                  43 | RUNNING   | 2018-05-17 16:17:12 | REPEATABLE READ     | NULL                                                                                         |
+——–+———————+———–+———————+———————+———————————————————————————————-+
2 rows in set (0.00 sec)

当trx_query为NULL时意味着事务处于空闲状态。

 


在MySQL5.7包括之后的版本可以通过启用Performance Schema之后,通过查询sys schema得知
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_transactions_current';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'transaction';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT thd_id, conn_id, user, db, command, trx_latency, trx_state, trx_autocommit, current_statement, last_statement FROM sys.session WHERE trx_state IS NOT NULL\G
*************************** 1. row ***************************
thd_id: 74
conn_id: 48
user: root@localhost
db: db1
command: Sleep
trx_latency: 18.59 m
trx_state: ACTIVE
trx_autocommit: NO
current_statement: NULL
last_statement: SELECT * FROM employees.employees WHERE emp_no = 12345
*************************** 2. row ***************************
thd_id: 68
conn_id: 42
user: root@localhost
db: db1
command: Query
trx_latency: 480.81 us
trx_state: ACTIVE
trx_autocommit: YES
current_statement: SELECT thd_id, conn_id, user, … on WHERE trx_state IS NOT NULL
last_statement: NULL
*************************** 3. row ***************************
thd_id: 80
conn_id: 54
user: root@localhost
db: world
command: Query
trx_latency: 154.07 ms
trx_state: ACTIVE
trx_autocommit: YES
current_statement: LOAD DATA LOCAL INFILE 'demo.t … ABLE 'demo' CHARACTER SET utf8
last_statement: NULL
3 rows in set (0.07 sec)

 

当然也可以通过SHOW ENGINE INNODB STATUS和InnoDB Monitor的方式
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2018-05-17 16:11:12 0x7fdd88f3c700 INNODB MONITOR OUTPUT
=====================================

————
TRANSACTIONS
————
Trx id counter 7397
Purge done for trx's n:o < 7075 undo n:o < 0 state: running but idle
History list length 244
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 422064617000560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 422064616998736, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 7396, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 3125
MySQL thread id 56, OS thread handle 140589461645056, query id 41443 localhost 127.0.0.1 root executing
LOAD DATA LOCAL INFILE 'demo.txt' INTO TABLE 'demo' CHARACTER SET utf8
—TRANSACTION 6996, ACTIVE 30 sec
MySQL thread id 45, OS thread handle 140584640485120, query id 2224 localhost 127.0.0.1 root cleaning up
Trx read view will not see trx with id >= 7073, sees < 7073