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 -uroot -p123456 -A -t > F:\all_data.sql 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 -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
Tips: 生产脚本1 2 3 4 5 6 7 $vim /data/mysqlBackup/myBackup.sh#!/bin/bash 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 $ mysqlbinlog -d [database] mysqld-bin.000001 > events.sql $ mysqlbinlog --debug-check mysqld-bin.000001 -r test.di $ mysqlbinlog -H mysqld-bin.000001 > binlog-hex-dump.out $ mysqlbinlog -o 10 mysqld-bin.000001 $ mysqlbinlog -j 15028 mysqld-bin.000001 > from-15028.out $ mysqlbinlog --start-datetime="2017-08-16 10:00:00" --stop-datetime="2018-08-17 10:00:00" > mysqld-bin.000001 $ mysqlbinlog mysqlbin000002 --start-position=510 --stop-position=1312 -r pos.sql # 在使用二进制日志文件进行数据库恢复时,该过程中也会产生日志文件,就会进入一个循环状态,继续恢复该过程中的数据 $ mysqlbinlog -D mysqld-bin.000001 -r dis.sql # 使用 -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 $ mysqlbinlog --read -from-remote-server --host=192.168.101.2 -p mysqld-bin.000001
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] # 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 optionsdefaults-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' /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 #服务器和客户端之间最大能发送的可能信息包;越大备份越快