Mysql服务器间迁移

set global max_allowed_packet=300000000;
set global net_buffer_length=300000;
set global interactive_timeout=35900000;
set global wait_timeout=35900000;

SELECT table_name, data_length + index_length AS len, table_rows,
     CONCAT(ROUND((data_length + index_length)/1024/1024,2),'MB') AS size,table_comment
FROM information_schema.tables
WHERE table_schema = 'xhkjedu_question'
ORDER BY len DESC;

mysqldump -u root -p123456 testdb > /data/db/testdb.sql

mysqldump -u root -p123456 testdb tablename> dbname_users.sql

scp /data/db/testdb.sql root@111.111.111.111:/data/db/testdb.sql

scp -r /data/db/* root@111.111.111.111:/data/db/

mysql -uroot -p

create database testdb;
use testdb;
set names utf8;
source testdb.sql

mysql -u root -p 123456 testdb < "/data/db/testdb.sql"

mysqldump -d -uroot -p12345678 xhkj_ques_0923 > xhkj_ques_0923_base.sql

mysql -u root -p
mysql> create database xhkj_ques_0924;
mysql> use xhkj_ques_0924;
mysql> set names utf8;
mysql> source xhkj_ques_0923_base.sql
mysql> quit

rm -rf /var/lib/mysql-files/*.csv
ls /var/lib/mysql-files/

select table_name from information_schema.tables where table_schema='xhkj_ques_0923' and table_type='base table';

#!/bin/bash
while read line
do
echo "======> export $line"
mysql -uroot -p12345678 xhkj_ques_0923 -e "SELECT * INTO OUTFILE '/var/lib/mysql-files/$line.csv' FIELDS TERMINATED BY ',' FROM $line"
done < tables.txt

chmod +x mysql_export.sh
sh ./mysql_export.sh

@echo off & setlocal enabledelayedexpansion

for /f %%i in (tables.txt) do ( set table=%%i
echo "======> !table!"
mysql -uroot -p12345678 xhkj_ques_0923 -e "SELECT * INTO OUTFILE 'F:/MySQL/Uploads/!table!.csv' FIELDS TERMINATED BY ',' FROM !table!"
)
pause

SET GLOBAL FOREIGN_KEY_CHECKS = 0;

SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,' ;') 
FROM information_schema.TABLE_CONSTRAINTS c 
WHERE c.TABLE_SCHEMA='库名' AND c.CONSTRAINT_TYPE='FOREIGN KEY';

#!/bin/bash
while read line
do
echo "======> import $line"
mysql -uroot -p12345678 xhkj_ques_0924 -e "LOAD DATA INFILE '/var/lib/mysql-files/$line.csv' INTO TABLE $line FIELDS TERMINATED BY ','"
done < tables.txt

chmod +x mysql_import.sh
sh ./mysql_import.sh

SET GLOBAL FOREIGN_KEY_CHECKS = 1;

show variables LIKE '%secure_file_priv%';

# secure_file_priv的值没有具体值时，mysqld的导入或导出不限制文件目录
secure_file_priv =

# 限制mysqld 的导入或导出只能在指定目录下
secure_file_priv = 指定目录

select concat(
    'select "',
    TABLE_name,
    '", count(*) from ',
    TABLE_SCHEMA,
    '.',
    TABLE_name,
    ' union '
) from information_schema.tables
where TABLE_SCHEMA='testdb';

use information_schema;
select table_name,table_rows from tables
where TABLE_SCHEMA = 'testdb'
order by table_name asc;

use information_schema;
select table_name,table_rows from tables
where TABLE_SCHEMA = 'testdb'
order by table_rows desc;

USE information_schema;
SELECT SUM(TABLE_ROWS) FROM TABLES WHERE TABLE_SCHEMA = 'testdb';

vi /etc/my.cnf

[mysqldump] 
max_allowed_packet = 1G