mysql批量修改字符集排序规则和批量修改数据库引擎和批量备份数据库

跟随趋势,本站mysql全面转换为InnoDB存储引擎,同时转换成utf8mb4_unicode_ci字符集.
mysql数据表使用不同字符集在join的时候不走索引,必须修正一下,统一查询才能高效
下面的查询语句有助于批量转换,节省手动修改时间
注意: 执行下面查询语句构建的结果,才能批量转换

mysql单数据库下数据表批量转换字符集排序规则

SELECT CONCAT('ALTER TABLE `',TABLE_SCHEMA, '`.`', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') from information_schema.`TABLES` WHERE TABLE_SCHEMA = 'your_db_name';

mysql多数据库下数据表批量转换字符集排序规则(推荐)

SELECT CONCAT('ALTER TABLE `',TABLE_SCHEMA, '`.`', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') from information_schema.`TABLES` WHERE TABLE_SCHEMA NOT IN ( 'mysql','sys', 'information_schema', 'performance_schema' ) ;

mysql单数据库批量修改默认字符集排序规则

SELECT DISTINCT CONCAT('ALTER DATABASE `',TABLE_SCHEMA,'` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') from information_schema.`TABLES` WHERE TABLE_SCHEMA = 'your_db_name';

mysql多数据库批量修改默认字符集排序规则(推荐)

SELECT DISTINCT CONCAT('ALTER DATABASE `',TABLE_SCHEMA,'` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') from information_schema.`TABLES` WHERE TABLE_SCHEMA NOT IN ( 'mysql','sys', 'information_schema', 'performance_schema' ) ;

mysql单数据库下数据表批量修改数据库引擎

SELECT CONCAT('ALTER TABLE `',TABLE_NAME,'` ENGINE=InnoDB;') from information_schema.`TABLES` WHERE TABLE_SCHEMA = 'your_db_name';

mysql多数据库下数据表批量修改数据库引擎(推荐)

SELECT CONCAT('ALTER TABLE `',TABLE_SCHEMA, '`.`', TABLE_NAME,'` ENGINE=InnoDB;') from information_schema.`TABLES` WHERE TABLE_SCHEMA NOT IN ( 'mysql','sys', 'information_schema', 'performance_schema' ) ;

mysql单数据库修改数据库引擎

SELECT DISTINCT CONCAT('ALTER DATABASE `',TABLE_SCHEMA,'` ENGINE=InnoDB;') from information_schema.`TABLES` WHERE TABLE_SCHEMA = 'your_db_name';

mysql多数据库批量修改数据库引擎(推荐)

SELECT DISTINCT CONCAT('ALTER DATABASE `',TABLE_SCHEMA,'` ENGINE=InnoDB;') from information_schema.`TABLES` WHERE TABLE_SCHEMA NOT IN ( 'mysql','sys', 'information_schema', 'performance_schema' ) ;

查看引擎不是innodb的所有表

SELECT DISTINCT TABLE_NAME,
    table_schema,
    ENGINE
FROM
    information_schema.tables
WHERE ENGINE
    != 'innodb' AND table_schema NOT IN(
        'sys',
        'mysql',
        'information_schema',
        'performance_schema'
    );

查看引擎不是innodb的所有数据库

SELECT DISTINCT 
    table_schema,
    ENGINE
FROM
    information_schema.tables
WHERE ENGINE
    != 'innodb' AND table_schema NOT IN(
        'sys',
        'mysql',
        'information_schema',
        'performance_schema'
    );

mysql批量导出数据库备份

mysql -e "show databases;" -uroot -p| grep -Ev "Database|sys|information_schema|mysql|test|performance_schema" | xargs mysqldump -uroot -p --single-transaction --skip-lock-tables --master-data=2 --default-character-set=utf8mb4  --databases > db_backup.sql

单独备份

mysqldump -uroot -p123456 --databases livelu | gzip  >  /root/backuplivelu/livelu$(date +%F-%H-%M-%S).sql.gz
#删除30天以上备份
find /root/backuplivelu -mtime +30 -name '*.sql.gz' -exec rm -rf {} \;