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终端备份数据库乱码(指定编码以及导出的文件路径)


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 下数据库表的列数和行数


# [[email protected] 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数据库:[[email protected] 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 #服务器和客户端之间最大能发送的可能信息包;越大备份越快