0x01 命令列表

(1) mysql
(2) mysqldump
(3) mysqlbinlog
(4) mysqladmin
(5) mysqlsafe
(6) mysqlshow
(7) mysqld

1.mysql 命令
1
2
3
4
5
#案例
mysql -h 127.0.0.1 -uroot -p123456 -P 3306 -e "show databases;"         #非交互执行mysql命令
mysql -uroot -p123456 -S '/data/3307/mysql.sock'   #指定Socket登陆

mysql -uroot -p123456 --default-character-set=utf8 [数据库表] < tb.sql  #到入数据库导到指定库中并设置导入的字符集


2.mysqldump 命令
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#常见选项：
--all-databases, -A： 备份所有数据库
--databases, -B： 用于备份多个数据库，如果没有该选项mysqldump把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldum把每个名字都当作为数据库名。
--force, -f：在导出过程中忽略出现的SQL错误.
--host=host_name, -h host_name：备份主机名，默认为localhost
--port=port_num, -P port_num：制定TCP/IP连接时的端口号
--no-data, -d：只导出表结构
--quick, -q：快速导出
--tables：覆盖 --databases or -B选项，后面所跟参数被视作表名
--xml, -X：导出为xml文件
--quick : 用于转储大的表,强制mysql从服务器一次一行的检索而不是检索所有行，并输出前CACHE到内存中；
--no-create-info : 不创建CREATE TABLE 语句;
--extended-insert : 使用包括几个VALUES列表的多行INSERT语法，这样文件更小,IO也小导入数据时会非常快
--default_character-set = latin1 : 按照原有字符集导出数据,这样导出的文件中,所有中文都是可见的,不会保存成乱码
--flush-logs,-F：在备份前刷新MySQL服务器的日志(刷新bin-log),此选项需要RELOAD权限
--compress, -C : 在客户端和服务器之间启用压缩传递所有信息
--events, -E: 导出事件。
--hex-blob:使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB
--pipe(windows系统可用)：使用命名管道连接mysql
--ignore-table：不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名、
--master-data：该选项将binlog的位置和文件名追加到输出文件中。
如果为1将会输出CHANGE MASTER 命令；用于主从复制,告诉从库从主库哪个log文件进行POS点更新。
如果为2输出的CHANGE MASTER命令前添加注释信息; 主要用于增量备份;
如:CHANGE MASTER TO MSATER_LOG_FILE='MYSQL-BIN.00002',MASTER_LOG_POS=1191

基础实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysqldump -uroot -p123456 --databases 数据库1 数据库2 > xxx.sql  #MySQLdump常用
mysqldump -uroot -p123456 -A -t > F:\all_data.sql        #备份全部数据库的数据(加 -t 参数)

mysqldump -uroot -p123456 --default-character-set=latin1 -d [DatabaseName] > alltable.sql  #备份单个数据库的结构并指定导出的字符集 

mysqldump -uroot -p123456 --events -B [数据库名]|gzip > /opt/back/bak.sql.gz
mysqldump -usystem -p --quick --no-create-info --extended-insert [数据库] > alldata.sql

mysqldump -uroot -p'123456' -S /data/3306/mysql.sock -A -B  -F --events --master-data=2 | gzip >/opt/rep.sql.gz  #在锁表的时候进行mysqldump导出数据库

mysqldump -uroot -p123456 -B database --tables table1 table2 table3  -r db_script.sql #导出指定表的数据及结构

mysqldump -uroot -p123456 -B database --tables --ignore-table=database.table1 --ignore-table=database.table2 -r req.sql.gz  #指定数据库中导出除被忽略的表的所有表

mysqldump -uroot -p --default-character-set=UTF8 --databases test_db --tables tbl_test --hex-blob --result-file=E:\Temp\tbl_test.sql #解决ps终端备份数据库乱码(指定编码以及导出的文件路径)

Tips: 生产脚本

1
2
3
4
5
6
7
$vim /data/mysqlBackup/myBackup.sh
#!/bin/bash
# Description: 每天备份然后删除十天前的。
execTime=$(date +%Y%m%d)
/data/mysql/bin/mysqldump -uweiyigeek.top -pki.54Nnei --default-character-set=utf8 --databases site | gzip >/data/mysqlBackup/new/site-${execTime}.sql.gz
find /data/mysqlBackup/new -type f -name "*.sql.gz" -mtime +10 >> /data/mysqlBackup/history.txt
find /data/mysqlBackup/new -type f -name "*.sql.gz" -mtime +10 -delete


3.mysqlbinlog 命令
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
$ mysqlbinlog mysqld-bin.000001 -r backup.sql               #读取binlog日志里面增删改等等SQL语句 到指定文件中
$ mysqlbinlog -d [database] mysqld-bin.000001 > events.sql   #获取特定数据库条目 使用 --database 命令，效果相同。 
$ mysqlbinlog --debug-check mysqld-bin.000001 -r test.di     #mysqlbinlog输出调试信息
$ mysqlbinlog -H mysqld-bin.000001 > binlog-hex-dump.out    #使用 -H 选项来获得给定的二进制日志文件的十六进制转储
$ mysqlbinlog -o 10 mysqld-bin.000001                        #跳过前N个条目 -o 选项。o代表偏移。
$ mysqlbinlog -j 15028 mysqld-bin.000001 > from-15028.out   #从一个特定位置提取条目位置编号为15028 # at 15028

$ mysqlbinlog --start-datetime="2017-08-16 10:00:00" --stop-datetime="2018-08-17 10:00:00" > mysqld-bin.000001 #特定开始/结束时间的条目内的sql语句
$ mysqlbinlog mysqlbin000002 --start-position=510 --stop-position=1312 -r pos.sql     #输出初始位置为510，结束位置为1312，导出到sql文件

#在使用二进制日志文件进行数据库恢复时，该过程中也会产生日志文件，就会进入一个循环状态，继续恢复该过程中的数据
$ mysqlbinlog -D mysqld-bin.000001 -r dis.sql    #禁止恢复过程产生日志  使用 --disable-log-bin 命令，效果相同

#使用 -server-id 指定mysql服务器，确保是由给定服务器id的mysql服务器所生成的日志。
$ mysqlbinlog --server-id=1 -r output.log mysqld-bin.000001 


#使用base64-output选项，可以控制输出语句何时是输出base64编码的BINLOG语句。
#decode-rows：这个选项将把基于行的事件解码成一个SQL语句，特别是当指定-verbose选项时
$ mysqlbinlog --base64-output=[never/always/decode-rows/auto] mysqld-bin.000001 > test.sql 


#在本地机器上，还可以读取位于远程服务器上的mysql二进制日志文件。为此需要指定远程服务器的ip地址、用户名和密码
$ mysqlbinlog -R -h 192.168.101.2 -u root -p mysqld-bin.000001  #-R 选项指示mysqlbinlog命令从远程服务器读取日志文件
$ mysqlbinlog --read-from-remote-server --host=192.168.101.2 -p mysqld-bin.000001  #-p 进行密码认证


4.mysqladmin 命令
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysqladmin -u root -p 'rootmysql' PASSWORD 'new_mysql_pass'  #重新设置mysql账号密码
mysqladmin -u root -p 'rootmysql' PASSWORD 'new_mysql_pass' -S /data/3306/mysql.sock

mysqladmin -u root -p 'rootmysql' shutdown  #关闭停止mysql
mysqladmin -u root -p 'rootmysql' -S /data/3306/mysql.sock shutdown


mysqladmin -uroot -p'weiyigeek'  -i 2 -c 5 status   #-i每个两秒查看一次服务器的状态总共 -c 重复5次
#Uptime: 88  Threads: 1  Questions: 2  Slow queries: 0  Opens: 0  Flush tables: 2  Open tables: 26  Queries per second avg: 0.022

mysqladmin -uroot -p'weiyigeek' kill 7,8    #--杀掉某个客户端的连接 ID值在processlist获取

mysqladmin -utest -ptest1 [create|drop] Databasename   #--创建数据库和删除数据库 

mysqladmin -uroot -p'weiyigeek' ping #判断服务是否启动  mysqld is alive
version     #--查看mysql版本
variables    #--查看所有的全局变量
processlist   #--查询所有mysql连接的客户端 
extended-status #--查询服务器拓展的状态
reload             #--重载授权表
flush-privileges   #--刷新权限
refresh     #--刷新所有表缓存，并关闭和打开log


6.mysqlshow 命令
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
./mysqlshow -uroot -p               #--显示服务器上的所有数据库
./mysqlshow -uroot -p database      #--显示数据库daba-test下有些什么表：
./mysqlshow -uroot -p daba-test -v  #--统计daba-test 下数据库表列的汇总
./mysqlshow -uroot -p daba-test -v -v  #--统计daba-test 下数据库表的列数和行数


# [root@izwz9biz2m4sd3bb3k38pgz mysql]# mysqlshow -uroot -pweiyigeek  iot -v -v
# Database: iot
# +----------+----------+------------+
# |  Tables  | Columns  | Total Rows |
# +----------+----------+------------+
# | iot_user |        5 |          1 |
# | test     |        1 |          2 |
# +----------+----------+------------+
# 2 rows in set.
7.mysqld 命令
1
2
#初始化 mysqld mysql数据库：[root@localhost mysql] 此处有账号密码
mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/

0x02 my.cnf 配置文件参数

描述：Linux机器下常用的配置文件路径

1
2
3
4
5
6
7
文件名     	作用
/etc/my.cnf	Global options
/etc/mysql/my.cnf	Global options (as of MySQL 5.1.15)
SYSCONFDIR/my.cnf	Global options
$MYSQL_HOME/my.cnf	Server-specific options
defaults-extra-file	The file specified with--defaults-extra-file=path  
~/my.cnf	User-specific options

优先级：在没有添加任何启动命令的时候，mysql会默认的依次从上到下检查配置文件是否存在，并且使用第一个发现的文件作为启动文件。

查看MySQL启动参数的几种方法：

1
2
3
4
5
6
7
8
#进程信息
ps ax | grep '[m]ysqld'

#根据进程的文件信息来查看
cat /proc/$(pidof mysqld)/cmdline | tr '\0' '\n'

#使用mysqld自带的 --verbose功能
/usr/sbin/mysqld --help --verbose --skip-networking --pid-file=$(tempfile) 2>/dev/null |grep -A1 'Default

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
[mysql]
default-character-set=utf8    #设置客户端默认字符集
socket=/data/3306/mysql.sock   #客户端的socket文件路径



[mysqld]
user    = mysql           #启动用户
port    = 3306            #启动端口  
server-id = 3306
basedir = /usr/local/mysql          #mysql安装根目录
datadir = /data/3306/data          #数据存放目录
pid-file = /data/3306/mysqld.pid   #存放运行后的PID值即进程号
socket  = /data/3306/mysql.sock   #socket文件路径


##日志.start
log-error = /data/mysql-error.log   #错误日志设置
log-slow-queries=/data/3306/slow-log.log  #慢查询记录到认证文件
##日志.END


binlog_format = ROW   #三种 Binlog 的日志模式。
##binlog日志.start
log-bin = /data/3306/binlog      #bin-log日志
log_bin_index = /var/lib/mysql/mysql-bin.index  #指定索引文件的位置
expire_logs_days = 7 #删除超出这个变量保留期之前的binlog全部日志被删除
max_binlog_size =  n   #binary log 最大的大小
binlog_cache_size =  n  #当前的多少事务cache在内存中
binlog_cache_disk_use = n #当前有多少事务暂存在磁盘上的，如果这个值有数值的话，就应该要注意调优了
replication-ignore-db = mysql  #主从忽略的库
replication-do-db = testdb #需要复制的数据库
binlog-do-db  = testdb   #binlog需要记录的库
binlog_ingore_db = mysql,information_schema  #忽略binlog指定得数据库使用,分割
max_binlog_cache_size = n  #最大能有多少事务cache在内存中
##binlog日志.END


##innoDB引擎重要参数.START
default-storage-engine=INNODB   # 创建新表时将使用的默认存储引擎
innodb_file_per_table = 0  #要不要把每个表分成文件
innodb_data_file_path = ibdata1:1024M:autoextend  #物理数据文件路径以及分割大小
innodb_data_home_dir = /data/3306/data/        #设置innodb独立表空间对于的物理数据文件目录
innodb_flush_log_at_trx_commit = 2    #log buffer每秒就会被刷写日志文件到磁盘,提交事务的时候不做任何操作
innodb_additional_mem_pool_size = 16M   #存储metdata(元数据信息)内存池大小
innodb_buferr_pool_size = 2048M   #缓存池大小(比较重要/不要设置太大 一般15%)
innodb_file_io_threads = 4    #文件IO线程
innodb_write_io_threads = 8  #异步操作的读写线程
innodb_read_io_threads = 8
innodb_thread_concurrency = 8  #并发的参数
innodb_log_buffer_size =16M    #innoDB日志存储区参数
innodb_log_file_size = 128M
innodb_log_file_in_group = 3
innodb_lock_wait_timeout = 120   #锁表等待实际
##innoDB引擎重要参数.END


##MyISAM引擎重要参数.START
key_buffer_size = 2048M    # #在MyISAM引擎中设置缓存区大小 ,混合引擎,设置 较大的 buffer 值： 5.5 >= 默认是MyISAM引擎
##MyISAM引擎重要参数.END


##缓存参数.START (建议不要设置太大)
query_cache_size  = 2M #缓存的大小
query_cache_limit = 1M #缓存的限制
query_cache_min_res_unit = 2k #缓存的对象
##缓存参数.END


##性能优化参数.START
max_connections=200  #允许最大连接数 
wait_timeout   #服务器关闭它之前在一个连接上等待行动的秒数
interactive_timeout  #服务器在关闭之前在一个交互连接上等待行动的秒数l
sort_buffer_size=2M   #排序缓存，一个线程占用一个，不能太大
tmp_table_size=256M #临时表会占用磁盘空间
max_heap_table_size=256 # 最大的堆表
long_query_time=2   #慢查询最大2s
##性能优化参数.END


##数据库安全参数.START
default_authentication_plugin=mysql_native_password  #设置默认身份验证插件 caching_sha2_password
default_password_lifetime=120  #MySQL会从启动时开始计算时间密码过期时间都为120天
##性数据库安全参数.START


character-set-server = utf8   #服务端默认字符集

skip-name-resolve          #主从复制跳过主机网络名称解析（防止show processlist没有权限认证）
explicit_defaults_for_timestamp  #设置默认时区
skip-grant-tables             #此项是为了装好后免登陆修改密码使用

[mysqldump]
max_allowed_packet = 16M #服务器和客户端之间最大能发送的可能信息包;越大备份越快