数据库迁移系列-xx社康通mysql数据库迁移实施记录

数据库迁移系列-xx社康通mysql数据库迁移实施记录

初始化数据库
/mysql/app/mysql/bin/mysqld --defaults-file=/mysql/data/3307/my.cnf --initialize-insecure --user=mysql --basedir=/mysql/app/mysql --datadir=/mysql/data/3307/data
/mysql/app/mysql/bin/mysqld_safe --defaults-file=/mysql/data/3307/my.cnf  &
/mysql/app/mysql/bin/mysqld  --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data

defaultmysqlpwd=`grep 'A temporary password' /mysql/log/3307/itpuxdb-error.err | awk -F "root@localhost:" '{ print $2}' | tail -n1`
echo "/mysql/app/mysql/bin/mysqld_safe --defaults-file=/mysql/data/3307/my.cnf        --user=mysql    &"    > /mysql/app/mysql/bin/mysql3307.start
chown mysql:mysql /mysql/app/mysql/bin/mysql3307.start
chmod +x /mysql/app/mysql/bin/mysql3307.start

连接数据库
mysql -uroot -pYxxxzx2024_1

mysql -uroot -p -P 3307 -S /mysql/data/3307/mysql.sock

1、查看MYSQL数据库服务器和数据库字符集
方法一：
show variables like'%character%';
方法二:
show variables like 'collation%';

2、查看MYSQL所支持的字符集
使用show charset可查看MYSQL所支持的字符集
show charset;

3、查看库的字符集
语法：show create database 数据库\G;
show databases;
show create database sys\G;

4、查看表的字符集
语法：show table status from 库名 like  表名;
show table status from test like 'app_clinic_record';

5、查看表中所有列的字符集
语法：show full columns from 表名;

6、创建用户
CREATE USER 'dsmhappadmin'@'localhost' IDENTIFIED BY 'XINS9NIMKs2UGNYO';
GRANT ALL PRIVILEGES ON *.* TO 'dsmhappadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

7、查询用户权限
SHOW GRANTS FOR 'dsmhappadmin'@'%';
SELECT * FROM mysql.user WHERE User='dsmhappadmin' AND Host='%';

查看数据库级别权限：
SELECT * FROM mysql.db WHERE User='username' AND Host='hostname';

查看表级别权限：
SELECT * FROM mysql.tables_priv WHERE User='username' AND Host='hostname';

8、授权dba权限给用户
GRANT ALL PRIVILEGES ON *.* TO 'dsmhappadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

9、查看数据库大小
查看所有库大小
SELECT table_schema AS `Database`, SUM(data_length + index_length) AS `Size`
FROM information_schema.TABLES
GROUP BY table_schema;
查看指定表大小
SELECT table_name, concat(round(data_length / (1024 * 1024), 2), 'MB') AS DataSize,
    concat(round(index_length / (1024 * 1024), 2), 'MB') AS IndexSize
FROM information_schema.tables 
WHERE table_schema = 'hsdb' AND table_name = 'hs_score_2021';

找出指定数据库大于1G的表
select TABLE_NAME,DATA_LENGTH, concat(round(((DATA_LENGTH+index_LENGTH)/1024/1024/1024), 2), 'G')  as data from information_schema.TABLES where TABLE_SCHEMA='hsdb' and DATA_LENGTH > 1000000000;

10、备份操作
--导出所有业务数据库共7个（结构）ddd dsmhappdb dsmhappdb_uat hsdb test xxl_job xxl_job_uat
mysqldump -udsmhappadmin -pXINS9NIMKs2UGNYO --no-data --databases ddd dsmhappdb dsmhappdb_uat test xxl_job xxl_job_uat > /u01/backup/mysql/db_jiegou_only.sql
--导出所有业务数据库共7个（结构+函数、存储过程、调度事件）
mysqldump -udsmhappadmin -pXINS9NIMKs2UGNYO --no-data  -R -E --databases ddd dsmhappdb dsmhappdb_uat hsdb  test xxl_job xxl_job_uat > db_jiegou_only.sql

--导出所有业务数据库共7个（库、表结构、数据、函数、存储过程、调度事件）
mysqldump -udsmhappadmin -pXINS9NIMKs2UGNYO -R -E --databases ddd dsmhappdb dsmhappdb_uat hsdb test xxl_job xxl_job_uat  >fulldb_data.sql


--全库函数和事件导出
mysqldump -udsmhappadmin -pXINS9NIMKs2UGNYO -R -E  -ndt --databases ddd dsmhappdb dsmhappdb_uat  test xxl_job xxl_job_uat  >alldbs_hanshuevent.sql

11、恢复操作
--导入数据库
关闭二进制日志
mysql8.0关闭binlog需要注释掉相关行，然后加skip-log-bin,对它的位置有严格要求，一定放在后面。
#log_bin=/mysql/log/3306/binlog/itpuxdb-binlog
#log_bin_index=/mysql/log/3306/binlog/itpuxdb-binlog.index
#binlog_format='ROW’
#binlog_rows_query_log_events=on
skip-log-bin

启用大内存页
vi /etc/sysctl.conf --大内存页为2048K，所以根据你的内存进行设置，我的MySQL innodb_buffer_pool_size设置了10G，所以大内存页为20480/2M=10240
vm.nr_hugepages=10240
[mysqld]
large-pages
根据环境的物理内存设置innodb_buffer_pool_size,一般设置物理内存的50-80%，以32G为例，设置16G，此值不能大于大内存页所占空间
innodb_buffer_pool_size = 16384M
为例导入加入，开下面两个值，导入完毕后，修改为1.
set global innodb_flush_log_at_trx_commit=0;
set global sync_binlog=0;

--导入全库结构
mysql -udsmhappadmin -pXINS9NIMKs2UGNYO < /backup/db_jiegou_only.sql
--导入全库数据
mysql -udsmhappadmin -pXINS9NIMKs2UGNYO  < /backup/dbfullbakup.sql

-----------------------------------------------------------------

导大表结构(-d 导结构)
mysqldump -udsmhappadmin -pXINS9NIMKs2UGNYO  -d hsdb hs_user_info > /u01/backup/mysql/02hs_user_info_jiegou.sql
mysqldump -udsmhappadmin -pXINS9NIMKs2UGNYO  -d hsdb hs_core_info > /u01/backup/mysql/01hs_core_info_jiegou.sql

scp -r /u01/backup/mysql/02hs_user_info_jiegou.sql /backup/
目标创建数据库，参考源库
show create database hsdb  --源库
--目标库
CREATE DATABASE`hsdb` /*!40100 DEFAULT CHARACTER SET utf8 */;
use hsdb
source /backup/02hs_user_info_jiegou.sql

导表数据( --extended-insert批量插入)
mysqldump -udsmhappadmin -pXINS9NIMKs2UGNYO --extended-insert -t hsdb hs_user_info > /u01/backup/mysql/02hs_user_info_data.sql

mysqldump -udsmhappadmin -pXINS9NIMKs2UGNYO --extended-insert -t hsdb hs_core_info > /u01/backup/mysql/01hs_user_core_data.sql

--------------------------------------------------

use information_schema;
批量生成结构语句
select concat('mysqldump -udsmhappadmin -pXINS9NIMKs2UGNYO  -d ',table_schema,' ',table_name,' > /u01/backup/mysql/hsdb/crea_',table_schema,'_',table_name,'jiegou.sql') from tables where table_schema='hsdb';

批量生成表数据语句
/mysql/app/mysql/bin/mysqld_safe --defaults-file=/mysql/data/3307/my.cnf --datadir='/mysql/data/3306/data' &

修改结构文件
vi 01hs_core_info_jiegou.sql.old
  PRIMARY KEY (`HS_CORE_INFO_UID`),
  KEY `IDX_USER_UID_TYPE`(`HS_USER_UID`,`CORE_TYPE`) USING BTREE COMMENT'来源类型_机构ID索引，
  KEY`IDX_CORE_TYPE` (CORE_TYPE`),
  KEY`IDX_CDATE` (CDATE`) USING BTREE,
  KEY｀IDX_HS_CORE_INFO`(HS_USER_UID`,`ORG_RECURE_UID`,`STATE`),
  KEY`IDX_RECURE_CDATE`(`ORG_RECURE_UID`,`AMT`,`CDATE`) USING BTREE,
  KEYIDX DETAIL_UID`HS_CORE_USE_DETAIL_UID`) USING BTREE,
  KEY `IDX_TYPE_ADD_TIME`(`ADD_TIME`,`CORE_TYPE`) USING BTREE COMMENT'用于统计>指标表'
--------------------------------
  PRIMARY KEY (`HS_USER_INFO_UID`),
  UNIQUE KEY `IDX_UN_HS_USER_INFO`(CARD_TYPE`,CARD_ID`,`STATE`),
  KEY`IDX_HS_USER_UID`(HS_USER_UID`),
  KEY｀IDX_USER_RECURE`(HS_USER_UID`,`ORG_RECURE_UID`),
  KEY`IDX_ORG_RECURE_UID`(`ORG_RECURE_UID`)
--------------------------------
将PRIMARY KEY和UNIQUE KEY保留
其他KEY删除，然后导入后，手动创建index。
重建命令如下：
alter table hs_core_info add index IDX_USER_UID_TYPE(`HS_USER_UID`,`CORE_TYPE`) USING BTREE, ALGORITHM=INPLACE,LOCK=NONE;
alter table hs_core_info add index IDX_CORE_TYPE(CORE_TYPE`),ALGORITHM=INPLACE,LOCK=NONE;

----------------------------------------------------------------------------
导出单个数据库并排除指定表（结构）
mysqldump -udsmhappadmin -pXINS9NIMKs2UGNYO  -d hsdb --ignore-table=hsdb.hs_user_info --ignore-table=hsdb.hs_core_info --ignore-table=hsdb.hs_score_2018 > /u01/backup/mysql/hsdb_jiegou.sql

导出单个数据库并排除指定表（表数据，批量提交）
mysqldump -udsmhappadmin -pXINS9NIMKs2UGNYO --extended-insert -t hsdb --ignore-table=hsdb.hs_user_info --ignore-table=hsdb.hs_core_info --ignore-table=hsdb.hs_score_2018   > /u01/backup/mysql/hsdb_data.sql

---------------------------------------------------------------------------
导用户和权限
mysqldump -udsmhappadmin -pXINS9NIMKs2UGNYO mysql user > /backup/user.sql
mysql -B -N -udsmhappadmin -pXINS9NIMKs2UGNYO  -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user NOT IN('replication','root','','mysql.session','mysql.sys')" mysql > mysql_users.txt

while read line; do mysql -B -N -udsmhappadmin -pXINS9NIMKs2UGNYO -e "SHOW GRANTS FOR $line"; done < mysql_users.txt > mysql_all_users_sql.sql

对上面生成sql脚本文件进行修改
在每行的结尾增加;做为结束符
sed -i 's/$/;/' mysql_all_users_sql.sql
在最后一行增加 "flush privileges;" 来刷新权限
sed -i '$a flush privileges;' mysql_all_users_sql.sql
在目标mysql上导入user用户及权限
将上面处理过的sql脚本文件/tmp/user.sql、/tmp/mysql_all_users_sql.sql 在目标mysql实例上执行
注意：如果不想导入root@localhost等用户，需要编辑/tmp/user.sql，将不想导入的用户手工删掉。