msyql数据迁移

夜晚的星光。

已于 2023-07-11 18:43:27 修改

阅读量120

点赞数

文章标签： mysql 数据库

于 2023-07-07 17:02:09 首次发布

本文链接：https://blog.csdn.net/weixin_46119350/article/details/131600670

版权

安装mysql5.7.17

1，下载mysql安装包，打包的glibc类型的包都为免编译的包
mkdir /app
mkdir -p /data/mysql/
cd /app
wget -c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

2，解压下载的数据库安装包
tar zxvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.7.17-linux-glibc2.5-x86_64 mysql

3，创建mysql用户
useradd -M -s /sbin/nologin  mysql

4，初始化数据库
cd /app/mysql/bin
./mysqld --initialize --user=mysql --basedir=/app/mysql --datadir=/data/mysql/data

初始化完成之后下面会带有数据库的密码保留记录一下为了方便成功登陆数据库修改密码。
[root@mysql bin]# ./mysqld --initialize --user=mysql --basedir=/app/mysql --datadir=/data/mysql/data
2018-01-22T04:29:24.761568Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-01-22T04:29:25.055226Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-01-22T04:29:25.106164Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-01-22T04:29:25.169516Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d3981dd8-ff2c-11e7-980f-001e67a6fc71.
2018-01-22T04:29:25.170529Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-01-22T04:29:25.171641Z 1 [Note] A temporary password is generated for root@localhost: %TlLrm/34D!5

5，把配置文件和启动程序拷贝到各自目录下。
mv /app/mysql/support-files/my-default.cnf /etc/my.cnf
mv /app/mysql/support-files/mysql.server /etc/init.d/mysqld

6，建立一些需要的文件夹和文件以及链接
mkdir -p /usr/local/mysql/bin/
ln -s /app/mysql/bin/mysqld /usr/local/mysql/bin/mysqld

mkdir -p /data/mysql/logs
cd /data/mysql/logs
touch mysql-error.log
chown -R mysql:mysql /data/mysql/logs/

mkdir -p /data/mysql/run
chown -R mysql:mysql /data/mysql/run

ln -s /app/mysql/bin/mysql /usr/bin/mysql

##mysql的配置文件有现成改好的，初始化完成之后可以直接替换。
cat > /etc/my.cnf <<EOF
[mysqld]
user = mysql
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

basedir = /app/mysql
datadir = /data/mysql/data
port = 3306
log-error= /data/mysql/logs/mysql-error.log

default-storage-engine = INNODB

wait_timeout = 31536000
interactive_timeout = 31536000
back_log = 500
max_connections = 20000
key_buffer_size = 402649088
sort_buffer_size = 16777208
slow_query_log = ON
slow_query_log_file = /data/mysql/logs/mysql_slow_query.log
long_query_time = 2
query_cache_size = 128M

read_buffer_size = 8M

#innodb_additional_mem_pool_size = 10M
innodb_data_file_path = ibdata1:76M:autoextend
#innodb_file_io_threads = 4
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 50M  #512
innodb_flush_method=O_DIRECT
innodb_log_file_size = 50M #512
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
table_open_cache = 10240

group_concat_max_len = 102400
EOF

7,启动mysql
[root@mysql mysql]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
成功！

8，登陆mysql
[root@mysql ~]# mysql -u root –p
密码为初始化数据库时生成的密码
登陆完成之后修改数据库密码以及设置访问权限
mysql> alter user user()  identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

数据迁移


1、创建目录,在新机器上
mkdir /data/db_backup

2、编写导出脚本
cat > mysql-out.sh <<EOF
#!/bin/bash
HOST=10.211.20.138
USER=root
PASS=123456
BACKUP_DIR=/data/db_backup
DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep -v "Database|information_schema|mysql|performance_schema|sys")

for DB in $DB_LIST; do
    BACKUP_NAME=$BACKUP_DIR/${DB}.sql
    if ! mysqldump -h$HOST -u$USER -p$PASS -B $DB > $BACKUP_NAME 2>/dev/null; then
        echo "$BACKUP_NAME 备份失败!"
    fi
done

3、执行脚本
bash mysql-out.sh

4、编写导入脚本
cat > mysql-in.sh <<EOF
#!/bin/bash
HOST=localhost
USER=root
PASS=123456
BACKUP_DIR=/data/db_backup

# 获取备份文件列表
BACKUP_FILES=$(ls -t $BACKUP_DIR/*.sql 2>/dev/null)

if [ -z "$BACKUP_FILES" ]; then
    echo "没有找到备份文件!"
    exit 1
fi

# 循环导入每个备份文件
for FILE in $BACKUP_FILES; do
    # 提取数据库名
    DB_NAME=$(basename "$FILE".sql | cut -d'.' -f1)

    #创建库
    if mysql -h$HOST -u$USER -p$PASS -e "CREATE DATABASE IF NOT EXISTS $DB_NAME"; then
        echo "成功创建数据库 $DB_NAME"
    else
        echo "创建数据库 $DB_NAME 失败!"
        continue
    fi
    
    # 导入数据
    if mysql -h$HOST -u$USER -p$PASS $DB_NAME < "$FILE"; then
        echo "成功导入 $FILE 到数据库 $DB_NAME"
    else
        echo "导入 $FILE 到数据库 $DB_NAME 失败!"
    fi
done
EOF

5、执行脚本
bash mysql-in.sh

###注意 库名如果有带 “-” 则会创建库失败，需要手动创建库，手动导入
#创建库
create database 库名;
#手动导入
mysql -h localhost -u root -p123456 库名 < /data/db_backup/库名.sql