mysql带库名查询_db mysql / mysql cluster 5.7.19 / reboot / devops

s

1、root账号起停数据库

[root@sftspstdb01 data]$ mysql_rds_start

[root@sftspstdb01 data]$ mysql_rds_stop

2、mysql账号分析慢日志

[mysql@sftspstdb01 data]$  /bak/percona-toolkit-3.0.5/bin/pt-query-digest /mysql/data/mysql_slow.log --since '2019-08-12 23:00:00' --until '2019-08-12 23:22:00' >>slow_log_ZZG.log

3、创建mysql cluster readonly账号

一主一从数据库，主库执行SQL命令(从库会复制过去)，navicat数据库工具root账号登陆，创建readonly账号命令如下：

[mysql@mydb01 ~]$ mysql -uroot -p123456 -h127.1

grant select on *.* to 账号@'%' identified by '密码';

-- 其中%为所有client IP地址均可以使用readonly账号连接进来

-- 若%为10.60.10.% ，表示10.60.10网段的client可以使用readonly账号连接进来

-- 其中*.* 表示所有库下所有表

4、给新数据库名称数据源赋予连接账号

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, EVENT, TRIGGER ON `数据库名称`.* TO '数据源用户'@'%';

5、备份数据库

方法一，带库名称导出

[mysql@ptsppstdb02 bin]$ /home/mysql/bin/mysqldump -h127.0.0.1 -uroot -p'密码' -B -F -R --triggers --events --single-transaction --databases ptsppst1 > /mysql/bak/ptsppst1_backup_20190919.sql

方法二，不带库名称导出，为导入新库名称准备，取消参数-B和--databases，增加 --set-gtid-purged=OFF，为主从复制保持一致，mysql 5.7.19

/usr/local/Percona-Server-5.7.19-17-Linux.x86_64.ssl101/bin/mysqldump

[mysql@ptsppstdb02 bin]$ /usr/local/Percona-Server-5.7.19-17-Linux.x86_64.ssl101/bin/mysqldump -h127.0.0.1 -uroot -p'密码' -F -R --triggers --events --single-transaction --set-gtid-purged=OFF ptsppst1 > /mysql/bak/ptsppst1_backup_20190919.sql

注：加了--set-gtid-purged=OFF时，在会记录binlog日志，如果不加，不记录binlog日志，所以在我们做主从用了gtid时，用mysqldump备份时就要加--set-gtid-purged=OFF，否则你在主上导入恢复了数据，主没有了binlog日志，同步则不会被同步。关于GTID是5.6以后，加入了全局事务 ID (GTID) 来强化数据库的主备一致性，故障恢复，以及容错能力。

官方给的：A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master).

[mysql@ptsppstdb02 bin]$

[mysql@sctssitdb04 ~]$ mysqldump -h127.1 -u fabu -p sctssitdeploy > /mysql/bak/sctssitdeploy20191213.sql

Enter password:

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

[mysql@sftssitdb01 etc]$ /usr/local/mysql/bin/mysqldump -h127.0.0.1 -uroot -p'123456' -B -F -R --triggers --events --single-transaction --databases sftsdeploy > /binlog/sftssit_backup_20200319.sql

mysqldump: [Warning] Using a password onthe command line interface can be insecure.

Warning: A partialdump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

6、导库单张表

[mysql@sctssitdb04 ~]$ mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

[mysql@sftssitdb06 ~]$ /usr/local/Percona-Server-5.7.19-17-Linux.x86_64.ssl101/bin/mysqldump -h127.0.0.1 -uroot -p'密码' --set-gtid-purged=OFF sftssitdmz perf_test_result_transactions > /binlog/20200622_perf_test_result_transactions.sql

导库

mysql> use 新库名称

mysql> source /mysql/bak/sctssitdeploy.sql

mysql导出和导入命令更改数据库名称数据库

https://www.cnblogs.com/wangzihong/p/10036840.html

https://www.cnblogs.com/mfrbuaa/p/4638563.html

https://www.jb51.net/article/49293.htm

二、mysql数据库名称改动

这个有个命令的

1.  命令格式

rename database 旧数据库名  to 新数据库名

注意：这个命令不是全部版本号的数据库都支持

2.  那么另一种方法：关闭mysql服务。进入到数据库存放文件夹，修文件夹名称。然后启动mysql就可以

个人观点 ：我还是不建议直接更改数据库名称。推荐新的数据库，然后导入

数据库扩盘

[root@sctssitdb21 ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/systemvg-rootlv

4.0G 727M 3.1G 19% /

tmpfs 3.9G 12K 3.9G 1% /dev/shm

/dev/vda1 194M 83M 102M 45% /boot

/dev/mapper/systemvg-homelv

4.0G 137M 3.7G 4% /home

/dev/mapper/systemvg-optlv

8.9G 1.2G 7.3G 15% /opt

/dev/mapper/systemvg-tmplv

1008M 34M 924M 4% /tmp

/dev/mapper/systemvg-usrlv

5.0G 3.3G 1.5G 70% /usr

/dev/mapper/systemvg-varlv

6.0G 195M 5.5G 4% /var

/dev/mapper/datavg-binloglv

20G 173M 19G 1% /binlog

/dev/mapper/datavg-datalv

127G 3.3G 118G 3% /mysql

[root@sctssitdb21 ~]# history

1 17:26:04 2019-01-10 shutdown -h now

2 17:22:47 2020-07-07 ls

3 17:22:52 2020-07-07 vgs

4 17:22:55 2020-07-07 df -h

5 17:23:10 2020-07-07 vgextend datavg /dev/vdc

6 17:23:12 2020-07-07 vgs

7 17:23:24 2020-07-07 lvextend +100G /dev/mapper/datavg-datalv

8 17:23:34 2020-07-07 lvextend -L +100G /dev/mapper/datavg-datalv

9 17:23:38 2020-07-07 resize2fs /dev/mapper/datavg-datalv

MySQL查询特定数据库中所有为空的表

select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA =’特定数据库’ and TABLE_ROWS = 0;

问题1：mysqldump: unknown variable 'set-gtid-purged=OFF'

解决1：版本太低，选择高版本

[root@sctssitdb21 bak]#/usr/bin/mysqldump --version

mysqldump Ver 10.13 Distrib 5.1.61, for redhat-linux-gnu (x86_64)

[root@sctssitdb21 bak]#locate mysqldump | more

/usr/local/Percona-Server-5.7.19-17-Linux.x86_64.ssl101/bin/mysqldump

end