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]$ /home/mysql/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 on the command line interface can be insecure.
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. 

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查询特定数据库中所有为空的表

https://blog.csdn.net/sinat_28527451/article/details/78120894

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

end