MySQL 数据库常用命令备份及还原管理

安装了MySQL默认是拒绝远程连接

1、回车,然后输入则使用了系统数据库

mysql -u root -p

2、修改 root 用户任何主机访问

第一种方法

update user set Host = '%' where User = 'root';

第二种方法

update mysql.user set host='%' where Host='localhost' AND User='root';

3、修改 root 密码

update mysql.user set authentication_string=password('123456')where user='root';
 刷新一下系统权限flush privileges;

4、添加新用户

新建本地登录

CREATE USER 'testuser'@'localhost' IDENTIFIED BY '123456';

CREATE USER 'testuser'@'127.0.0.1' IDENTIFIED by '123456';

新建远程登录

CREATE USER 'testuser'@'%' IDENTIFIED BY '123456'; 

5、为用户授权,添加用户远程访问该数据库的全部权限

授权格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";

6、刷新一下系统权限 flush privileges

第一种命令执行

grant all privileges on *.* to 'testuser'@'%' identified by "123456" with grant option;

第二种直接登录MySQL数据库和命令一起执行

testuser=testuser
testdb=123456
mysql -u"root" -e "grant all privileges on *.* to '${testuser}'@'%' identified by \"${testdb}\" with grant option;"

7、创建数据库

create database IF NOT EXISTS dbtest default character set utf8mb4 collate utf8mb4_unicode_ci; 

8、显示MYSQL中所有数据库

 mysql -u"root" -p"123456" -e "show databases;" 

9、授权 testuser 用户拥有 dbtest 数据库的所有权限

grant all privileges on dbtest.* to 'testuser'@'127.0.0.1' identified by "123456" with grant option;刷新一下系统权限flush privileges;

10、指定部分权限给 testuser 用户

grant select,update on dbtest.* to 'testuser'@'127.0.0.1' identified by "123456";

11、显示数据库中所有用户 -D 选择数据库

mysql -u"root" -p"123456" -Dmysql -e "select User,Host from user;"

12、给予查询权限

GRANT Select ON *.* TO 'testuser'@'localhost';

13、添加插入权限

grant insert on dbtest.* to 'testuser'@'localhost'; 

14、添加删除权限

grant delete on dbtest.* to 'testuser'@'localhost'; 

15、检查用户是否添加成功

use mysql;select User,authentication_string,Host from user;

或者

use mysql;SELECT user, host FROM mysql.user ORDER BY user;

16、删除用户

DROP USER 'testuser'@'localhost';DROP USER 'testuser'@'%'; |

17、修改指定用户密码

update mysql.user set authentication_string=password("新密码") where User="testuser" and Host="localhost"; 

刷新一下系统权限

flush privileges;

18、查看 bin-log与server-id是否开启

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文件

mysqlbinlog mysql-bin.000001 -d dbtest > dbtest.sql

20、将binlog日志文件选择时间范围导出dbtest库导出成sql文件

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代表换行展示

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000011 -d db1 > db1.sql 

22、生成新的 binlog 文件, -F

有时候会希望导出数据之后生成一个新的binlog文件,只需要加上-F参数即可

mysqldump -u"root" -p"123456" --databases db1 -F > db1.sql 

23、只导出数据库表结构不导出数据

mysqldump -u"root" -p"123456" -d --databases db1 > db1.sql

导出多个数据库的结构

mysqldump -u"root" -p"123456" -d -B --databases db1 db2 > db1.sql

24、只导出数据不导出结构

mysqldump -u"root" -p"123456" -t --databases db1 > db1.sql

25、导出数据库列表,单个库时可省略

mysqldump -u"root" -p"123456" -B --databases "db1" -table "table1" > db1.sql

26、 导出数据库自定义涵数

mysqldump -u"root" -p"123456" -R --databases "db1" > db1_func.sql

27、 导出数据库 自定义涵数不导出数据

mysqldump -u"root" -p"123456" -ntd -R --databases "db1" > func_db.sql

其它常用选项:

-d(--no-data) 不导出任何数据,只导出数据库表结构
-t(--no-create-info) 只导出数据,而不添加CREATE TABLE 语句
-n(--no-create-db) 只导出数据,而不添加CREATE DATABASE 语句
-E(--events) 导出事件
--trigger默认导出触发器,使用--skip-triggers屏蔽导出
-B--databases:导出数据库列表,单个库时可省略
R(--routines) 导出存储过程以及自定义函数
-ntd导出存储过程

28、跨服务器导出导入数据

mysqldump --host=h1 -u"root" -p"123456" --databases db1 |mysql --host=h2 -u"root" -proot db2

将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中,db2的数据库必须存在否则会报错, 加上 -C 参数 可以启用压缩传递

mysqldump --host=192.168.80.137 -u"root" -p"123456" -C --databases test |mysql --host=192.168.80.133 -u"root" -p"123456" dbtest

29、将主库的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

mysqldump -u"root" -p"123456" --dump-slave=1 --databases db1 > db1.sql

30、将当前服务器的binlog的位置和文件名追加到输出文件,--master-data

该参数和 --dump-slave 方法一样,只是它是记录的是当前服务器的 binlog,相当于执行 show master status,状态(file,position)的值。

注意:--master-data 不会停止当前服务器的主从服务

31、--opt

等同于 --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启, 可以用 --skip-opt 禁用.

mysqldump -u"root" -p"123456" --host=localhost --all-databases --opt > db.sql

32、保证导出的一致性状态--single-transaction

该选项在导出数据之前提交一个BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎(它不显示加锁通过判断版本来对比数据),仅InnoDB。本选项和--lock-tables 选项是互斥的,因为 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话,应结合使用 --quick 选项。不缓冲查询,直接导出到标准输出。默认为打开状态,使用 --skip-quick 取消该选项。

--quick, -q

33、--lock-tables, -l

开始导出前,锁定所有表。用 READ LOCAL 锁定表以允许 MyISAM 表并行插入。对于支持事务的表例如 InnoDB 和 BDB,--single-transaction 是一个更好的选择,因为它根本不需要锁定表。

请注意当导出多个数据库时,--lock-tables 分别为每个数据库锁定表。因此,该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。

34、导出存储过程和自定义函数--routines, -R

mysqldump -u"root" -p"123456" --host=localhost --all-databases --routines

35、初始化从服务器数据,在主服务上进行备份,导出所有数据库

mysqldump --single-transaction --master-data --triggers --routines --all-databases -u"root" -p"123456" > all.sql

36、导出所有数据库,该命令会导出包括系统数据库在内的所有数据库

mysqldump -u"root" -p"123456" --all-databases > all.sql

37、导出多个数据库 db1、db2 两个所有数据,多个数据库用空格

mysqldump -u"root" -p"123456" --databases db1 db2 > db1db2.sql

38、导出数据库 db1 中 a1 a2 两个表所有数据

注意:导出指定表只能针对一个数据库进行导出,且导出的内容中和导出数据库也不一样,导出指定表的导出文本中没有创建数据库的判断语句,只有删除表-创建表-导入数据

mysqldump -u"root" -p"123456" 数据库 --tables 数据表 > tables.sql
mysqldump -u"root" -p"123456" --databases db1 --tables a1 a2 > a1a2.sql 

39、条件导出,导出db1表a1中id=1的数据

如果多个表的条件相同可以一次性导出多个表,字段是整形

mysqldump -u"root" -p"123456" --databases db1 --tables a1 --where='id=1' > a1.sql

40、字段是字符串,并且导出的sql中不包含drop table,create table

mysqldump -u"root" -p"123456" --databases --no-create-info --databases db1 --tables a1 --where="id='a'" > a1l.sql

41、导出数据库 db1 中 a1 a2 两个表所有数据

mysqldump -u"root" -p"123456" --databases --no-create-info --databases db1 --tables a1 --where="id='a'" > a1.sql

*其它常用选项:**

--skip-comments注释信息(默认存在)
--no-create-info取消创建表sql(默认存在)
--no-create-db不导出数据(默认导出)
--no-create-db取消创建数据库sql(默认存在)
--add-drop-database增加删除数据库sql(默认不存在)
--skip-add-locks取消在每个表导出之前增加LOCK TABLES(默认存在锁)
--skip-add-drop-table取消每个数据表创建之前添加drop数据表语句(默认每个表之前存在drop语句)

42、导出数据库 忽略表

mysqldump -u"root" -p"123456" --databases dbtest --ignore-table=数据库.数据表 > bak.sql

43、导出数据库 指定行

mysqldump -u"root" -p"123456" --databases dbtest --tables 数据表 --where="条件" > bak.sql

或者用limit限制结果集

mysqldump -u"root" -p"123456" --databases dbtest --tables 数据表 --where="条件 LIMIT 条数" > bak.sql

44、导出远程服务器导出 所有数据库

mysqldump -u"root" -p"123456" -h218.85.157.99 --all-databases --routines --events --triggers > bak.sql

45、导出远程服务器导出 指定数据库

mysqldump -u"root" -p"123456" -h218.85.157.99 --databases db1 > all.sql

46、用于与其他服务器合并数据的备份

mysqldump -u"root" -p"123456" --databases dbtest --skip-add-drop-table --replace > bak.sql

--skip-add-drop-table: 不会将drop table语句写入导出文件中。

--replace:将使用replace into语句而不是insert语句导出。

47、并行处理,通过指定线程数量加速备份过程

mysqlpump --default-parallelism=8 > all.sql 

--skip-add-drop-table: 不会将drop table语句写入导出文件中。

--replace:将使用replace into语句而不是insert语句导出。

48、指定每个数据库的线程数

mysqlpump -u root -p --parallel-schemas=4:数据库 --default-parallelism=2 > all.sql

49、备份排除或包含数据库。

对以 t 结尾的所有数据库进行备份,多个数据库用逗号分隔,数据库名可以使用%或_通配符。除此之外,还有类似 --include-events,--include-routines,--include-tables,--include-triggers,--include-users 等

mysqlpump -u root -p --include-databases=%t > bak.sql 

排除以 a 开头的数据库进行备份,多个数据库用逗号分隔,数据库名可以使用%或_通配符。

除此之外,还有类似 --exclude-events,--exclude-routines,--exclude-tables,--exclude-triggers,--exclude-users 等

mysqlpump -u root -p --exclude-databases=a% > bak.sql 

50、备份用户

mysqlpump -u root -p --exclude-databases=% --users > user.sql

可以通过--exclude-users来排除某些用户

mysqlpump --exclude-databases=% --exclude-users=root --users > user.sql

51、压缩备份

通过使用 --compress-output = lz4 或 --compress-output = zlib

mysqlpump -u root -p --compress-output=lz4 > all.lz4mysqlpump -u root -p --compress-output=zlib > all.zlib

** 通过如下语句进行解压 **

lz4_decompress all.lz4 all.sqlzlib_decompress all.zlib all.sql 

52、防火墙设置一下,不然3306端口还是无法访问

iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

53、备份数据库

- 第一种方法
mysqldump -u"root" -p"123456" "dbtest" > dbtest.sql;
- 第二种方法,线上环境导出和导入数据参数
mysqldump -u"root" -p"123456" -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 备份文件的目录

xtrabackup --defaults-file=/etc/my.cnf \
--host=主机IP \
--user=用户名 \
--password=密码 \
--port=3306 \
--backup \
--parallel=3 \
--target-dir=增量备份目录 \
--incremental-basedir=全量备份目录

** 增量备份是基于全量备份的,--incremental-basedir 指向全量备份目录 **

在MySQL数据库中,备份支持增量备份的。如果每次都使用全量备份,很消耗性能不说,还占用磁盘空间。 - 增量备份是依靠 mysql-bin 日志生成增量数据,因此,需要打开 mysql-bin 日志

[mysqld] binlog_format = MIXED             #binlog日志格式 
log_bin = /data/mysql/log/mysql-bin.log    #binlog日志名 
expire_logs_days = 7                       #binlog过期清理时间 
全量备份
mysqldump -u"root" -ppassword --all-databases --flush-logs --delete-master-logs --single-transaction | gzip >> /data/backup/all_dbs_2019_10_07.sql.gz
  1. --flush-logs 为结束当前日志,生成新日志文件;
  2. --delete-master-logs 清除之前的日志,注意,备份之前最好讲日志也增量备份一下
  3. --single-transaction 保证备份的一致性 ,采用会话隔离
增量备份 在全量备份的时候,已经之前的日志清楚,因此只要备份mysqlbin日志就可以。
rsync -avy /data/mysql/log/mysql-bin.log* /data/backup/mysql
备份恢复 先全量恢复,将全量压缩文件解压,然后使用mysql命令进行导入
source /data/backup/all_dbs_2019_10_07.sql

然后在使用mysqlbin将日志解析成sql语句导入

mysqlbin /data/backup/mysql-bin.000030 | mysql -u root

备份很重要,主要经过多次测试再上线。不然数据丢失,就只能跑路了。

54、还原数据库

完整备份所有数据库:

sqldump -u"root" -p"123456" --all-databases > all.sql 

在 mysql8 之前,存储过程和事件存储在 mysql.proc 和 mysql.event 表中。

从mysql8 开始,相应对象的定义存储在数据字典中,这些表不会被备份。

要将存储过程和事件也包含,请使用如下语句:

sqldump -u"root" -p"123456" --all-databases --routines --events > all.sql

55、还原数据库

** 第一种方法: **

mysql -u"root" -p"123456" -hlocalhost "dbtest" < dbtest.sql

** 第二种方法: 这个我没有测试 **

gunzip -c abc.sql.gz \|mysql -u"root" -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 文件。

mysqldump -u root -p --all-databases --routines --events --single-transaction --master-data > all.sql

--master-data = 2 表示在导出过程中,记录当前库的binlog和pos点,并在导出文件中注释这一行
--master-data = 1 表示在导出过程中,记录当前库的binlog和pos点,并在导出文件中不注释这一行

56、在从库导出时,记录主库的二进制日志位置

--master-data 将服务器的二进制日志的位置输出到 sql 文件

mysqldump -u root -p --all-databases --routines --events --single-transaction --dump-slave > all.sql

--dump-slave = 2 表示在导出过程中,记录主库的binlog和pos点,并在导出文件中注释这一行。

--dump-slave = 1 表示在导出过程中,记录主库的binlog和pos点,并在导出文件中不注释这一行。

57、防火墙设置一下,不然3306端口还是无法访问

iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

58、设置完之后,查看一下是否能通过。

iptables -L -n

CentOS 设置MySQL的远程访问

59、如果想要限制访问。

iptables -D INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
OK,这样就可以远程访问了。以后有时间在排版