跳到主要内容

mysqldump

一、 使用 mysqldump 需要一定的权限​

  1. select (必须)
  2. show view (如果数据库中有视图的话，就需要赋予 show view 权限)
  3. lock tables (如果使用 --lock-tables 或 --single-transaction 参数的话，需要有 lock tables 权限)
  4. reload (如果你使用 --flush-logs 参数来备份二进制日志文件，那么你需要 RELOAD 权限以执行该操作。)
  5. super (如果你需要备份所有数据库，包括系统数据库（如 mysql 数据库），则需要 SUPER 权限。)
GRANT SELECT, SHOW VIEW, LOCK TABLES, RELOAD ON database_name.* TO 'username'@'localhost';

二、 mysqldump 命令​

  • -u 指定用户名 eg: -u root
  • -p 指定密码 eg: -proot
  • -h 指定ip eg: 127.0.0.1
  • -P --port 指定端口 eg: -P 3306 --port=3306
  • --database 指定要导出的数据库，可以指定多个数据库，使用空格分隔。 eg: --databases db1 db2 db3
  • --all-databases 可以导出所有的数据库。
  • > /mysqldump/db_01.sql 数据输出到指定文件中
  • --verbose 输出详情
  • --single-transaction 参数的作用是在导出数据的过程中启用事务，确保导出的数据是一个一致的快照，即使在导出的过程中有其他数据库操作也不会导致数据不一致。
    • 数据库在运行时，启用 --single-transaction 可以确保导出的数据是一个一致的快照，而不会受到其他写操作的影响。
    • 避免锁表，使用 --single-transaction 可以避免锁定整个表，而只锁定需要导出的数据。这对于在线系统非常有用，因为它允许其他用户继续访问表而不会受到锁定的影响。
    • 使用 --single-transaction 可以提高 mysqldump 的导出速度。
  • --ingore-table 指定不导出的表。如果需要不导出多张表，可以使用多个 --ignore-table。

2.1、导出本机指定数据库​

mysqldump -u eiscp -p eiscp > eiscp_back.sql

2.2、导出远程机器上的指定数据库​

mysqldump --verbose --single-transaction  -uusername -ppassword \ 
-h 127.0.0.1 -P 3306 --databases db_01 > /mysqldump/db_01.sql

2.3、导出指定数据库时，不导出指定表​

mysqldump -u username -p --ignore-table=db_01.table01 \ 
--ignore-table=db_01.table02 your_database_name > backup.sql

2.4、只导出指定表​

mysqldump -u username -p your_database_name your_table_name > backup.sql
mysqldump  --single-transaction  -uzxbeiscp -p -h 123.60.216.225 \ 
-P 3308 zxbeiscp eiscp51_vfs > /wangzhy/eiscp51_vfs.sql
mysqldump  --single-transaction  -uzxbeiscp -p -h 123.60.216.225 \ 
-P 3308 --ignore-table=zxbeiscp.etl_log_bf \
--ignore-table=zxbeiscp.eiscp51_vfs \ 
--ignore-table=zxbeiscp.eiscp51_historytask \
--databases  zxbeiscp > /wangzhy/zxbeiscp.sql

2.5、数据导入​

mysql -uroot -p -h gz-cdb-7el9yfbd.sql.tencentcdb.com -P63610 \ 
--binary-mode=1 -e "source zxbeiscp.sql" zxbeiscp
mysql -uroot -p -h gz-cdb-7el9yfbd.sql.tencentcdb.com -P63610 \ 
esicp2 < /wangzhy/eiscp51_vfs.sql

三、troubleshooting​

3.1、执行 mysqldump 导出数据库的时候提示引用了不存在的表/字段/方法或 definer/invoker 缺少视图的权限。​

执行的命令

.\mysqldump.exe -u root -proot --port=3308 --force zxbeiscp --single-transaction > zxbeiscp.sql

具体报错

View 'zxbeiscp.eiscp51_emon_v_inactive' references invalid table(s) or column(s) or function(s) 
or definer/invoker of view lack rights to use them

解决方法

  1. 先执行下查询视图数据的方法，看是否有报错，如果有的话，就先解决这个报错。
  2. 检查权限
  3. 重新创建一下视图 create or replace xxxxx