执行 .sql 文件
方法1
mysql -h1.1.1.1 -P3306 -uadmin -ppwd db_name < test.sql
方法2
“1. 使用 mysql-client 登录到 mysql 服务器
- source /root/path/to/.sql
mysql 查看 binlog
mysql 提供了 mysqlbinlog 指令，用于查看binlog
先来一个小例子, 查看某一段时间的 bin log
mysqlbinlog -h[host] -P[port] -u[username] -p[password] --read-from-remote-server [binlog file name]
--base64-output=decode-rows --start-datetime='2017-11-02 00:00:00'
--stop-datetime='2017-11-02 00:10:00'
看了上面这个小例子，发现一个小问题: binlog file name 从哪来？
这个需要我们去mysql中去查看
mysql> mysql -u[username] -p[password] ## 登录mysql
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF | # 这个参数说明当前mysql 没有开启binlog 日志
+---------------+-------+
这里又引入一个问题: 如何开启 mysql 的 binlog
在 mysql 的 官方文档 中，对于 binlog 的开启只有一段内容
Binary logging is enabled by default (the log_bin system variable is set to ON). The exception is if you use mysqld to initialize the data directory manually by invoking it with the --initialize or --initialize-insecure option, when binary logging is disabled by default, but can be enabled by specifying the --log-bin option.
二进制日志是默认启用（系统变量log_bin默认设置为ON）。唯一的例外是如果你使用mysqld来初始化数据目录手动调用它的 --initialize 或 --initialize-insecure 选项，当二进制日志默认是禁用的，但可以通过指定 `--log-bin` 选项启用。
我们开始设置 mysql 的启动配置， 这里有两种方式
“1. 直接在启动命令行中添加参数
sudo /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --log-bin=mysql-bin --user=\_mysql
在 mysql 配置文件中增加配置信息
这个时候我们遇到一个问题: Mysql 的配置文件在哪？> sudo mysqld --verbose --help | grep -A2 "Default options are read from the following files in the given order:" Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
从上面可以看到 mysql 的配置文件如下：
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
在 /etc/my.cnf 添加配置如下
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
plugin-dir=/usr/local/mysql/lib/plugin
user=\_mysql
log-bin=mysql-bin
port=3306
我们来尝试启动 mysql，但是却没能启动成功， 报错信息如下
[ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation
遇到问题，我们再去看看官方文档，在官方文档中有如下介绍，在 mysql 5.7 及以上版本中，需要设置参数 server-id
In MySQL 5.7, a server ID had to be specified when binary logging was enabled, or the server would not start. The server_id system variable is set to 1 by default. The server can be started with this default ID when binary logging is enabled, but a warning message is issued if you do not specify a server ID explicitly using the --server-id option. For servers that are used in a replication topology, you must specify a unique non-zero server ID for each server.
在MySQL 5.7中，当启用二进制日志时，必须指定服务器ID，否则服务器将无法启动。系统变量 server_id 默认设置为1。当启用二进制日志记录时，服务器可以以这个默认ID启动，但如果未显式使用服务器ID选项指定服务器ID，则会发出警告消息。对于复制拓扑中使用的服务器，必须为每个服务器指定唯一的非零服务器ID。
将 /etc/my.cnf 配置修改如下：
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
plugin-dir=/usr/local/mysql/lib/plugin
user=\_mysql
log-bin=mysql-bin
port=3306
server-id=1
再次启动mysql，成功了！但是有个问题，mysql 的日志直接输出在控制台中，需要输出到指定文件夹中，在 /etc/my.cnf 的 [mysqld] 中增加配置
log-error=/usr/local/mysql/data/local.err
我们开启了binlog之后，可以使用 mysqlbinlog 来查看binlog了！
回到第一个问题 binlog file name 从哪来？
mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------------------------------------+
| mysql-bin.000065 | 516819919 | | | 63c58e9d-e063-11e6-8a0e-6c92bf324c90:1-19804498 |
+------------------+-----------+--------------+------------------+-------------------------------------------------+
1 row in set (0.00 sec)
这里第一列就是 binlog 日志文件的名称了！
mysqlbinlog
-?, --help Display this help and exit.
查看帮助信息
--base64-output=name
Determine when the output statements should be
base64-encoded BINLOG statements: 'never' disables it and
works only for binlogs without row-based events;
'decode-rows' decodes row events into commented
pseudo-SQL statements if the --verbose option is also
given; 'auto' prints base64 only when necessary (i.e.,
for row-based events and format description events). If
no --base64-output[=name] option is given at all, the
default is 'auto'.
--bind-address=name IP address to bind to.
--character-sets-dir=name
Directory for character set files.
-d, --database=name 指明查询binlog 的数据库，如：-dtest，查看test 数据库
--rewrite-db=name Rewrite the row event to point so that it can be applied
to a new database
-#, --debug[=#] This is a non-debug version. Catch this and exit.
--debug-check This is a non-debug version. Catch this and exit.
--debug-info This is a non-debug version. Catch this and exit.
--default-auth=name Default authentication client-side plugin to use.
-D, --disable-log-bin
Disable binary log. This is useful, if you enabled
--to-last-log and are sending the output to the same
MySQL server. This way you could avoid an endless loop.
You would also like to use it when restoring after a
crash to avoid duplication of the statements you already
have. NOTE: you will need a SUPER privilege to use this
option.
-F, --force-if-open Force if binlog was not closed properly.
(Defaults to on; use --skip-force-if-open to disable.)
-f, --force-read Force reading unknown binlog events.
-H, --hexdump Augment output with hexadecimal and ASCII event dump.
-h, --host=name Get the binlog from server.
-i, --idempotent Notify the server to use idempotent mode before applying
Row Events
-l, --local-load=name
Prepare local temporary files for LOAD DATA INFILE in the
specified directory.
-o, --offset=# Skip the first N entries.
-p, --password[=name]
Password to connect to remote server.
--plugin-dir=name Directory for client-side plugins.
-P, --port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-R, --read-from-remote-server
Read binary logs from a MySQL server. This is an alias
for read-from-remote-master=BINLOG-DUMP-NON-GTIDS.
--read-from-remote-master=name
Read binary logs from a MySQL server through the
COM_BINLOG_DUMP or COM_BINLOG_DUMP_GTID commands by
setting the option to either BINLOG-DUMP-NON-GTIDS or
BINLOG-DUMP-GTIDS, respectively. If
--read-from-remote-master=BINLOG-DUMP-GTIDS is combined
with --exclude-gtids, transactions can be filtered out on
the master avoiding unnecessary network traffic.
--raw Requires -R. Output raw binlog data instead of SQL
statements, output is to log files.
-r, --result-file=name
Direct output to a given file. With --raw this is a
prefix for the file names.
--secure-auth Refuse client connecting to server if it uses old
(pre-4.1.1) protocol. Deprecated. Always TRUE
--server-id=# Extract only binlog entries created by the server having
the given id.
--server-id-bits=# Set number of significant bits in server-id
--set-charset=name Add 'SET NAMES character_set' to the output.
-s, --short-form Just show regular queries: no extra info and no row-based
events. This is for testing only, and should not be used
in production systems. If you want to suppress
base64-output, consider using --base64-output=never
instead.
-S, --socket=name The socket file to use for connection.
--ssl-mode=name SSL connection mode.
--ssl Deprecated. Use --ssl-mode instead.
(Defaults to on; use --skip-ssl to disable.)
--ssl-verify-server-cert
Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
--ssl-ca=name CA file in PEM format.
--ssl-capath=name CA directory.
--ssl-cert=name X509 cert in PEM format.
--ssl-cipher=name SSL cipher to use.
--ssl-key=name X509 key in PEM format.
--ssl-crl=name Certificate revocation list.
--ssl-crlpath=name Certificate revocation list path.
--tls-version=name TLS version to use, permitted values are: TLSv1, TLSv1.1
--start-datetime=name
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
-j, --start-position=#
Start reading the binlog at position N. Applies to the
first binlog passed on the command line.
--stop-datetime=name
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--stop-never Wait for more data from the server instead of stopping at
the end of the last log. Implicitly sets --to-last-log
but instead of stopping at the end of the last log it
continues to wait till the server disconnects.
--stop-never-slave-server-id=#
The slave server_id used for --read-from-remote-server
--stop-never. This option cannot be used together with
connection-server-id.
--connection-server-id=#
The slave server_id used for --read-from-remote-server.
This option cannot be used together with
stop-never-slave-server-id.
--stop-position=# Stop reading the binlog at position N. Applies to the
last binlog passed on the command line.
-t, --to-last-log Requires -R. Will not stop at the end of the requested
binlog but rather continue printing until the end of the
last binlog of the MySQL server. If you send the output
to the same MySQL server, that may lead to an endless
loop.
-u, --user=name 链接数据库的用户名
-v, --verbose Reconstruct pseudo-SQL statements out of row events. -v
-v adds comments on column data types.
-V, --version 查看 `mysqlbinlog` 版本信息
--open-files-limit=#
Used to reserve file descriptors for use by this program.
-c, --verify-binlog-checksum
Verify checksum binlog events.
--binlog-row-event-max-size=#
The maximum size of a row-based binary log event in
bytes. Rows will be grouped into events smaller than this
size if possible. This value must be a multiple of 256.
--skip-gtids Do not preserve Global Transaction Identifiers; instead
make the server execute the transactions as if they were
new.
--include-gtids=name
打印binlog 信息时，只打印指定的部分全局事务id对应的binglog 信息
--exclude-gtids=name
打印binlog 信息时，排除部分全局事务id对应的binglog 信息
待解决问题
如果在 mac os 的设置中启动了mysql 而且勾选了 Automatically Start MySQL Server on Startup, 当你想要关闭 mysql 的时候，mysql 会自动重启！！！
这个问题找了好久才发现，至于这个不断重启功能的实现待研究…
mysql 查看存储过程
查看存储过程状态
1 | show procedure status; |
输出结果：
mysql> show procedure status\G
*************************** 1. row ***************************
Db: db_name
Name: procedure_name
Type: PROCEDURE
Definer: root@localhost
Modified: 2017-05-10 14:10:00
Created: 2017-05-10 14:10:00
Security_type: DEFINER
Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8_general_ci
1 row in set (0.02 sec)
查看存储过程具体定义
1 | show create procedure %procedure_name%; |
创建存储过程
注意：在创建存储过程的时候，语句中的分号会导致报错，需要用 DELIMITER 重新定义分隔符
1 | DELIMITER $$ |
删除存储过程
1 | DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name |
mysql 查看数据库和表容量大小
information_schema 库中有个 TABLES 表
mysql> show create table TABLES\G
*************************** 1. row ***************************
Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
`ENGINE` varchar(64) DEFAULT NULL,
`VERSION` bigint(21) unsigned DEFAULT NULL,
`ROW_FORMAT` varchar(10) DEFAULT NULL,
`TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
`AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_FREE` bigint(21) unsigned DEFAULT NULL,
`AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
`CREATE_TIME` datetime DEFAULT NULL,
`UPDATE_TIME` datetime DEFAULT NULL,
`CHECK_TIME` datetime DEFAULT NULL,
`TABLE_COLLATION` varchar(32) DEFAULT NULL,
`CHECKSUM` bigint(21) unsigned DEFAULT NULL,
`CREATE_OPTIONS` varchar(255) DEFAULT NULL,
`TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
其中主要字段是
TABLE_SCHEMA: 数据库名称TABLE_NAME: 表名称DATA_LENGTH: 数据占用的空间大小，单位是字节INDEX_LENGTH: 索引占用的空间大小，单位是字节
查看数据库占用空间大小
1 | SELECT (SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) / 1024 / 1024 |
查看表占用空间大小
1 | SELECT (SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) / 1024 / 1024 |
mysql 求时间差距
timestampdiff(unit,datetime_expr1,datetime_expr2) 比较两个 timestamp 时间间隔，返回单位是unit 的数值
例如
timestampdiff(second,'2018-01-01 00:00:00','2018-01-01 00:00:02') 得到 2
表结构常用修改语句
重置 AUTO_INCREMENT
1 | ALTER TABLE <Table Name> AUTO_INCREMENT = 1; |