数据库：mysql库迁移和导入导出

站内链接:

mysql 优化 optimizer

移动目录

操作

前置条件

a non-root user with sudo privileges.
a MySQL server

设置步骤
moving the MySQL data directory:

# Verify the current location by starting an interactive MYSQL session 或者查看/var/lib/mysql/my.conf配置
mysql -u root -p
> select @@datadir

# Shut down MYSQL to ensure the interity of the data.
sudo systemctl stop mysql
sudo systemctl status mysql

# Copy the existing database directory to the new location with "rsync"
sudo rsync -av /var/lib/mysql /mnt/data/

# Once the "rsync" is complete, rename the current folder with a .bak extension.
sudo mv /var/lib/mysql /var/lib/mysql.bak

Point to the new data location:

1
2
3

# Edit mysql configure files to reflect the new data directory
sudo vim /etc/mysql/my.conf
    datadir=/mnt/data/mysql

Configuring apparmor access control rules:

# Tell AppArmor to let MYSQL write the new directory by creating an alias between the default directory and
# the new location
sudo vim /etc/apparmor.d/tunables/alias
    alias /var/lib/mysql/ -> /mnt/data/mysql/,
# restart AppArmor
sudo systemctl restart apparmor

Restart MYSQL:

# Create the minimal directory structure to pass the script's enviroment check that scipt's name is:
# /usr/share/mysql/mysql-systemd-start
sudo mkdir /var/lib/mysql/mysql -p

# restart
sudo systemctl start mysql
sudo systemctl status mysql

# Make sure the new data directory is indeed in use.
mysql -u root -p
> select  @@datadir

Remove old directory:

1
2
3

sudo rm -Rf /var/lib/mysql.bak
sudo systemctl restart mysql
sudo systemctl status mysql

问题

错误:Aborting because of a corrupt database page in the system tablespace. Or, there

产生这种错误原因是在迁移之前发生过数据库所在磁盘满的情况导致的表空间损坏, 重启数据库之后无法正常恢复数据, 最终导致数据页读取不到需要的 page 数据.

数据导入和导出

数据和结构

导出数据库中所有表结构和数据, 注意, 此时导出的 SQL 中不包含数据库的创建

# a. 导出
mysqldump -u用户名 --set-default-charset=utf8 -p --databases 数据库名 > 数据库名1.sql
# 实例
mysqldump -u root -p xinshu_diary > xinshu_diary1.sql

# b. 导入(不提前创建数据库）
source 数据库名.sql

实例:

# 导出mysql docker容器中的数据
mysqldump --host=127.0.0.1 --port=3306 --default-character-set=utf8 --user=root --protocol=tcp --column-statistics=FALSE --skip-triggers bamboo2 -p > bamboo2.sql

# 导入
mysql --host=127.0.0.1 --port=3306 -uroot -p
# sql语句:
  use bamboo1
  source bamboo2.sql
  show tables

导出数据库中指定表结构和数据

# a. 导出指定表
mysqldump -u用户名 --set-default-charset=utf8 -p --databases 数据库名 表名1 表名2 表名3> 数据库名1.sql

# b. 使用配置文件default-files, chatgpt搜索: mysqldump: default-files配置文件内容
mysqldump --defaults-file=/home/user/my.cnf db_name > db_name.sql

下面是一个简单的配置文件示例:

[mysqldump]
user = root
password = xxx
host = 127.0.0.1
port = 3306
default-character-set = utf8
protocol = tcp
column-statistics = FALSE

例子:

cur_date=`date +%F`

# 1. 测试1
mysqldump --defaults-file=./online.cnf xinshu_diary order > /tmp/mysql/xinshu_diary_${cur_date}.sql

# 2. 使用上面的配置进行测试2
mysqldump --defaults-file=./mysqldump_test.conf --skip-triggers bamboo2 bamboo_topology bamboo_node > bamboo2.sql

表结构

若仅仅想导出数据库表结构而不包含数据的化, 可以增加--no-data选项以表示不到处数据

# 导出
mysqldump -u用户名 --set-default-charset=utf8 -p --no-data --databases 数据库名 > 数据库名1.sql

# 导入
mysqldump -uuser -hhosts  -p  -d 数据库名 < 数据库名.sql

示例如下:

# 1. 方式1
mysqldump --host=127.0.0.1 --port=3306 --default-character-set=utf8 --user=root --protocol=tcp --column-statistics=FALSE --skip-triggers --no-data -p bamboo2 > bamboo2.sql
# 2. 方式2
mysqldump --defaults-file=./mysqldump_test.conf --skip-triggers --no-data bamboo2 > bamboo2.sql