MySQL 数据库常用命令管理
安装了MySQL默认是拒绝远程连接的
1、回车，然后输入则使用了系统数据库
|
1 |
mysql -u root -p |
2、修改 root 用户任何主机访问
第一种方法
|
1 |
update user set Host = '%' where User = 'root'; |
第二种方法
|
1 |
update mysql.user set host='%' where Host='localhost' AND User='root'; |
3、修改 root 密码
|
1 2 3 4 |
update mysql.user set authentication_string=password('123456')where user='root'; 刷新一下系统权限 flush privileges; |
4、添加新用户
新建本地登录
|
1 |
CREATE USER 'testuser'@'localhost' IDENTIFIED BY '123456'; |
|
1 |
CREATE USER 'testuser'@'127.0.0.1' IDENTIFIED by '123456'; |
新建远程登录
|
1 |
CREATE USER 'testuser'@'%' IDENTIFIED BY '123456'; |
5、为用户授权,添加用户远程访问该数据库的全部权限
授权格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";
|
1 2 3 |
grant all privileges on *.* to 'testuser'@'%' identified by "123456" with grant option;" 刷新一下系统权限 flush privileges; |
第二种直接登录MySQL数据库和命令一起执行
#####testuser=testuser
#####testdb=123456,
|
1 2 3 4 |
mysql -uroot -e "grant all privileges on *.* to '${testuser}'@'%' identified by \"${testdb}\" with grant option;" 刷新一下系统权限 flush privileges; |
6、创建数据库
|
1 |
create database IF NOT EXISTS dbtest default character set utf8mb4 collate utf8mb4_unicode_ci; |
7、显示MYSQL中所有数据库
|
1 |
mysql -u"root" -p"123456}" -e "show databases;" |
8、授权 testuser 用户拥有 dbtest 数据库的所有权限
|
1 2 3 |
grant all privileges on dbtest.* to 'testuser'@'127.0.0.1' identified by "123456" with grant option; 刷新一下系统权限 flush privileges; |
9、指定部分权限给 testuser 用户
|
1 |
grant select,update on dbtest.* to 'testuser'@'127.0.0.1' identified by "123456"; |
10、显示数据库中所有用户 -D 选择数据库
|
1 |
mysql -u"root" -p"123456" -Dmysql -e "select User,Host from user;" |
11、给予查询权限
|
1 |
GRANT Select ON *.* TO 'testuser'@'localhost'; |
12、添加插入权限
|
1 |
grant insert on dbtest.* to 'testuser'@'localhost'; |
13、添加删除权限
|
1 |
grant delete on dbtest.* to 'testuser'@'localhost'; |
14、检查用户是否添加成功
|
1 2 |
use mysql; select User,authentication_string,Host from user; |
或者
|
1 2 |
use mysql; SELECT user, host FROM mysql.user ORDER BY user; |
15、删除用户
|
1 2 |
DROP USER 'testuser'@'localhost'; DROP USER 'testuser'@'%'; |
16、修改指定用户密码
|
1 2 3 4 |
update mysql.user set authentication_string=password("新密码") where User="testuser" and Host="localhost"; 刷新一下系统权限 flush privileges; |
17、刷新权限
|
1 |
flush privileges; |
18、查看 bin-log与server-id是否开启
|
1 2 |
mysql -u"root" -p"123456" -e "show variables like 'log_bin';" mysql -u"root" -p"123456" -e "show variables like 'server_id';" |
19、将binlog日志文件mysql-bin.000001 导出成sql文件
|
1 |
mysqlbinlog mysql-bin.000001 -d dbtest > dbtest.sql |
20、将binlog日志文件选择时间范围导出dbtest库导出成sql文件
|
1 |
mysqlbinlog --start-date="2019-10-12 15:00:19" --stop-date="2019-10-12 15:30:19" mysql-bin.000011 -d dbtest > dbtest.sql |
21、如果binlog日志文件记录为ROW格式,将row格式的binlog日志文件base64解析后转入文件，-v代表换行展示
|
1 |
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000011 -d db1 > db1.sql |
22、生成新的binlog文件, -F
有时候会希望导出数据之后生成一个新的binlog文件,只需要加上-F参数即可
|
1 |
mysqldump -uroot -p123456 --databases db1 -F > db1.sql |
23、只导出表结构不导出数据，--no-data
|
1 |
mysqldump -uroot -p123456 --databases db1 -F > db1.sql |
24、跨服务器导出导入数据
|
1 |
mysqldump --host=h1 -uroot -p123456 --databases db1 |mysql --host=h2 -uroot -proot db2 |
将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错, 加上 -C 参数 可以启用压缩传递
|
1 |
mysqldump --host=192.168.80.137 -uroot -p123456 -C --databases test |mysql --host=192.168.80.133 -uroot -p123456 test |
25、将主库的binlog位置和文件名追加到导出数据的文件中，--dump-slave
注意：--dump-slave命令如果当前服务器是从服务器那么使用该命令会执行stop slave来获取master binlog的文件和位置，等备份完后会自动执行start slave启动从服务器。但是如果是大的数据量备份会给从和主的延时变的更大，使用--dump-slave获取到的只是当前的从服务器的数据执行到的主的binglog的位置是（relay_mater_log_file,exec_master_log_pos),而不是主服务器当前的binlog执行的位置，主要是取决于主从的数据延时。
该参数在在从服务器上执行，相当于执行show slave status。当设置为 1 时，将会以 CHANGE MASTER 命令输出到数据文件；设置为 2 时，会在 change 前加上注释。该选项将会打开 --lock-all-tables，除非 --single-transaction 被指定。在执行完后会自动关闭 --lock-tables 选项。--dump-slave默认是: 1
|
1 |
mysqldump -uroot -p123456 --dump-slave=1 --databases db1 > db1.sql |
26、将当前服务器的binlog的位置和文件名追加到输出文件，--master-data
该参数和 --dump-slave 方法一样，只是它是记录的是当前服务器的 binlog，相当于执行 show master status，状态（file,position)的值。
注意：--master-data 不会停止当前服务器的主从服务
27、--opt
等同于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启, 可以用 --skip-opt 禁用.
|
1 |
mysqldump -uroot -p123456 --host=localhost --all-databases --opt > db.sql |
28、保证导出的一致性状态--single-transaction
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎（它不显示加锁通过判断版本来对比数据），仅InnoDB。本选项和--lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用 --quick 选项。不缓冲查询，直接导出到标准输出。默认为打开状态，使用 --skip-quick 取消该选项。
|
1 |
--quick, -q |
29、--lock-tables, -l
开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。
请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
30、导出存储过程和自定义函数--routines, -R
|
1 |
mysqldump -uroot -p123456 --host=localhost --all-databases --routines |
31、初始化从服务器数据,在主服务上进行备份,导出所有数据库
|
1 |
mysqldump --single-transaction --master-data --triggers --routines --all-databases -u"root" -p"123456" > all.sql |
32、导出所有数据库,该命令会导出包括系统数据库在内的所有数据库
|
1 |
mysqldump -uroot -p123456 --all-databases > all.sql |
33、导出多个数据库 db1、db2 两个所有数据,多个数据库用空格
|
1 |
mysqldump -uroot -p123456 --databases db1 db2 > db1db2.sql |
34、导出数据库 db1 中 a1 a2 两个表所有数据
注意:导出指定表只能针对一个数据库进行导出，且导出的内容中和导出数据库也不一样，导出指定表的导出文本中没有创建数据库的判断语句，只有删除表-创建表-导入数据
mysqldump -uroot -p123456 数据库 --tables 数据表 > tables.sql
|
1 |
mysqldump -uroot -p123456 --databases db1 --tables a1 a2 > a1a2.sql |
35、条件导出，导出db1表a1中id=1的数据
如果多个表的条件相同可以一次性导出多个表,字段是整形
|
1 |
mysqldump -uroot -p123456 --databases db1 --tables a1 --where='id=1' > a1.sql |
36、字段是字符串,并且导出的sql中不包含drop table,create table
|
1 |
mysqldump -uroot -p123456 --databases --no-create-info --databases db1 --tables a1 --where="id='a'" > a1.sql |
36、导出数据库 db1 中 a1 a2 两个表所有数据
|
1 |
mysqldump -uroot -p123456 --databases --no-create-info --databases db1 --tables a1 --where="id='a'" > a1.sql |
其它常用选项：
|
1 2 3 4 5 6 7 |
--skip-comments ---注释信息(默认存在) --no-create-info， ---取消创建表sql(默认存在) --no-data ---不导出数据(默认导出) --no-create-db， ---取消创建数据库sql(默认存在) --add-drop-database ---增加删除数据库sql（默认不存在） --skip-add-locks ---取消在每个表导出之前增加LOCK TABLES（默认存在锁） --skip-add-drop-table ---取消每个数据表创建之前添加drop数据表语句(默认每个表之前存在drop语句) |
37、导出数据库 忽略表
|
1 |
mysqldump -uroot -p123456 --databases dbtest --ignore-table=数据库.数据表 > bak.sql |
38、导出数据库 指定行
|
1 |
mysqldump -uroot -p123456 --databases dbtest --tables 数据表 --where="条件" > bak.sql |
或者用limit限制结果集
|
1 |
mysqldump -uroot -p123456 --databases dbtest --tables 数据表 --where="条件 LIMIT 条数" > bak.sql |
39、导出远程服务器导出 所有数据库
|
1 |
mysqldump -uroot -p123456 -h218.85.157.99 --all-databases --routines --events --triggers > |
40、导出远程服务器导出 指定数据库
|
1 |
mysqldump -uroot -p123456 -h218.85.157.99 --databases db1 > all.sql |
41、用于与其他服务器合并数据的备份
|
1 |
mysqldump -uroot -p123456 --databases dbtest --skip-add-drop-table --replace > bak.sql |
--skip-add-drop-table: 不会将drop table语句写入导出文件中。
--replace：将使用replace into语句而不是insert语句导出。
42、并行处理，通过指定线程数量加速备份过程
|
1 |
mysqlpump --default-parallelism=8 > all.sql |
--skip-add-drop-table: 不会将drop table语句写入导出文件中。
--replace：将使用replace into语句而不是insert语句导出。
43、指定每个数据库的线程数
|
1 |
mysqlpump -u root -p --parallel-schemas=4:数据库 --default-parallelism=2 > all.sql |
44、备份排除或包含数据库。
对以 t 结尾的所有数据库进行备份，多个数据库用逗号分隔，数据库名可以使用%或_通配符。除此之外，还有类似--include-events，--include-routines，--include-tables，--include-triggers，--include-users等
|
1 |
mysqlpump -u root -p --include-databases=%t > bak.sql |
45、防火墙设置一下，不然3306端口还是无法访问
排除以 a 开头的数据库进行备份，多个数据库用逗号分隔，数据库名可以使用%或_通配符。
除此之外，还有类似--exclude-events，--exclude-routines，--exclude-tables，--exclude-triggers，--exclude-users等
|
1 |
mysqlpump -u root -p --exclude-databases=a% > bak.sql |
46、备份用户
|
1 |
mysqlpump -u root -p --exclude-databases=% --users > user.sql |
可以通过--exclude-users来排除某些用户
|
1 |
mysqlpump --exclude-databases=% --exclude-users=root --users > user.sql |
47、压缩备份
通过使用--compress-output = lz4 或 --compress-output = zlib
|
1 2 |
mysqlpump -u root -p --compress-output=lz4 > all.lz4 mysqlpump -u root -p --compress-output=zlib > all.zlib |
通过如下语句进行解压
|
1 2 |
lz4_decompress all.lz4 all.sql zlib_decompress all.zlib all.sql |
48、防火墙设置一下，不然3306端口还是无法访问。
|
1 |
iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT |
49、备份数据库
第一种方法
|
1 |
mysqldump -u"root" -p"123456" "dbtest" > dbtest.sql; |
第二种方法，线上环境导出和导入数据参数
|
1 |
mysqldump -uroot -p123456 -q -Q --set-gtid-purged=OFF --default-character-set=utf8 --hex-blob --skip-lock-tables --databases abc 2> abc.err |gzip > abc.sql.gz |
第三种方法，使用 xtrabackup 进行备份
--defaults-file 数据库配置文件
--backup 执行备份操作
--parallel 备份时并发的线程数
--target-dir 备份文件的目录
|
1 2 3 4 5 6 7 8 9 |
xtrabackup --defaults-file=/etc/my.cnf \ --host=主机IP \ --user=用户名 \ --password=密码 \ --port=3306 \ --backup \ --parallel=3 \ --target-dir=增量备份目录 \ --incremental-basedir=全量备份目录 \ |
增量备份是基于全量备份的，--incremental-basedir 指向全量备份目录
50、还原数据库
完整备份所有数据库:
|
1 |
sqldump -uroot -p123456 --all-databases > all.sql |
在 mysql8 之前，存储过程和事件存储在 mysql.proc 和 mysql.event 表中。
从mysql8 开始，相应对象的定义存储在数据字典中，这些表不会被备份。
要将存储过程和事件也包含，请使用如下语句：
|
1 |
sqldump -uroot -p123456 --all-databases --routines --events > all.sql |
51、还原数据库
第一种方法:
|
1 |
mysql -uroot -p123456 -hlocalhost "dbtest" < dbtest.sql |
第二种方法: 这个我没有测试
|
1 |
gunzip -c abc.sql.gz |mysql -uroot -p -vvv -P3306 --default-character-set=utf8 abc 1> abc.log 2> abc.err |
第三种方法: 时间点恢复
要获得时间点恢复，应该指定--single-transaction 和 --master-data
--single-transaction 在备份之前，会将事务隔离级别设为REPEATABLE READ模式，并执行 START TRANSACTION 来提供一致的备份。
--master-data 将服务器的二进制日志的位置输出到 sql 文件。
|
1 |
mysqldump -u root -p --all-databases --routines --events --single-transaction --master-data > all.sql |
--master-data = 2 表示在导出过程中，记录当前库的binlog和pos点，并在导出文件中注释这一行。
--master-data = 1 表示在导出过程中，记录当前库的binlog和pos点，并在导出文件中不注释这一行。
52、在从库导出时，记录主库的二进制日志位置
--master-data 将服务器的二进制日志的位置输出到 sql 文件。
|
1 |
mysqldump -u root -p --all-databases --routines --events --single-transaction --dump-slave > all.sql |
--dump-slave = 2 表示在导出过程中，记录主库的binlog和pos点，并在导出文件中注释这一行。
--dump-slave = 1 表示在导出过程中，记录主库的binlog和pos点，并在导出文件中不注释这一行。
53、在从库导出时，记录主库的二进制日志位置
--master-data 将服务器的二进制日志的位置输出到 sql 文件。
|
1 |
mysqldump -u root -p --all-databases --routines --events --single-transaction --dump-slave > all.sql |
--dump-slave = 2 表示在导出过程中，记录主库的binlog和pos点，并在导出文件中注释这一行。
--dump-slave = 1 表示在导出过程中，记录主库的binlog和pos点，并在导出文件中不注释这一行。
54、防火墙设置一下，不然3306端口还是无法访问。
|
1 |
iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT |
55、设置完之后，查看一下是否能通过。
|
1 |
iptables -L -n |
56、如果想要限制访问。
|
1 |
iptables -D INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT |
OK，这样就可以远程访问了。以后有时间在排版
