Linux 系统安装 MySql 5.7 并测试进行数据库迁移

站长

2024年06月20日 15:44 · 阅读数 33

应用背景

此次为配合行内对现有 MySQL 数据库的改造迁移要求，项目组研究决定进行数据库迁移测试，并整理出该文档以供参考。

具体还要在测试环境或仿真环境造作并验证。

环境介绍

服务器版本
- 两台 Cent OS 7 服务器：master，slave，各有如下两个用户
  - root/123456
  - mysql/123456
数据库版本
- mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
- 地址：dev.mysql.com/get/Downloa…
迁移工具
- Xtrabackup工具（开源免费）
  - 备份速度快，物理备份可靠
  - 备份过程不会打断正在执行的事务无需锁表
  - 能够基于压缩等功能节约磁盘空间和流量
  - 自动备份校验
  - 还原速度快
  - 可以流传将备份传输到另外一台机器上
  - 在不增加服务器负载的情况备份数据

安装MySQL5.7

登录服务器

Connecting to 192.168.23.130:22...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.

Last login: Tue Apr 27 14:08:46 2021
[root@localhost ~]#

上传 mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz，查看

-rw-r--r--. 1 root root 665389778 Apr 27 11:01 mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
[root@localhost mysql]# pwd
/usr/local/mysql
[root@localhost mysql]#

解压

[root@localhost mysql]# tar -zxvf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
[root@localhost mysql]# ll
total 649796
drwxr-xr-x. 9 root root       129 Apr 27 11:04 mysql-5.7.34-linux-glibc2.12-x86_64
-rw-r--r--. 1 root root 665389778 Apr 27 11:01 mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
# 重命名
[root@localhost mysql]# mv mysql-5.7.34-linux-glibc2.12-x86_64 mysql-5.7.34

切换管理员用户

[mysql@localhost ~]$ su root
Password: 
[root@localhost mysql]#

创建组和用户

root@zgu-vm:/# groupadd mysql
root@zgu-vm:/# useradd -r -g mysql mysql

创建数据目录并修改权限

root@zgu-vm:/# mkdir -p /data/mysql/mysql
root@zgu-vm:/# mkdir -p /data/mysql/log/bin_log/bin_log
root@zgu-vm:/# mkdir -p /data/mysql/log/relay_log/relay_log
root@zgu-vm:/# mkdir -p /data/mysql/log/innodb_log/innodb_log
root@zgu-vm:/# chown mysql:mysql -R /data/mysql

配置 my.cnf

[root@localhost /]# vim /etc/my.cnf
[mysqld]
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/usr/local/mysql/mysql-5.7.34
datadir=/data/mysql/mysql
socket=/tmp/mysql.sock
server_id=1
log_bin=/data/mysql/log/bin_log/bin_log
relay_log=/data/mysql/log/relay_log/relay_log
innodb_log_group_home_dir=/data/mysql/log/innodb_log/innodb_log
log_error=/data/mysql/log/mysql.err
pid_file=/data/mysql/mysql.pid
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true

初始化数据库

# 进入以下目录
[root@localhost /]# cd /usr/local/mysql/mysql-5.7.34/bin/
# 初始化
[root@localhost bin]# ./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/mysql-5.7.34/ --datadir=/data/mysql/mysql/ --user=mysql --initialize

查看临时密码

[root@localhost bin]# cat /data/mysql/log/mysql.err 
2021-04-27T05:45:40.632022Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-04-27T05:45:40.662231Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-04-27T05:45:40.723123Z 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: ccd26f6f-a71b-11eb-912b-000c29973e9a.
2021-04-27T05:45:40.725871Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-04-27T05:45:41.500736Z 0 [Warning] CA certificate ca.pem is self signed.
2021-04-27T05:45:42.023779Z 1 [Note] A temporary password is generated for root@localhost: L36fv=0R0Y#d

启动MySQL

# 先将mysql.server放置到/etc/init.d/mysql中
[root@localhost bin]# cp /usr/local/mysql/mysql-5.7.34/support-files/mysql.server /etc/init.d/mysql

# 启动
[root@localhost bin]# service mysql start
Starting MySQL.. SUCCESS!

登录MySQL

# 第一次密码为：L36fv=0R0Y#d
[root@localhost bin]# ./mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

修改密码

mysql> SET PASSWORD =PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

添加远程访问权限

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

最后，使用客户端远程访问即可

防火墙开放 3306 端口

[root@localhost sysconfig]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@localhost sysconfig]# firewall-cmd --reload
success

建立MySQL链接文件

# 如果不希望每次都到bin目录下使用mysql命令则执行以下命令。因为系统默认会查找/usr/bin下的命令，由于mysql没有在这个目录下，所以出现not found。因此需要做一个软连接到/usr/bin目录下
ln -s /usr/local/mysql/mysql-5.7.34/bin/mysql /usr/bin

开机自启

[root@localhost ~]# chmod +x /etc/init.d/mysql
[root@localhost ~]# chkconfig --add mysql
[root@localhost ~]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysql          	0:off	1:off	2:on	3:on	4:on	5:on	6:off
netconsole     	0:off	1:off	2:off	3:off	4:off	5:off	6:off
network        	0:off	1:off	2:on	3:on	4:on	5:on	6:off
[root@localhost ~]# 
[root@localhost ~]# reboot

重启后查看

[root@localhost ~]# netstat -na | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

master中创建数据库和表

创建数据库和表

DROP DATABASE IF EXISTS PMSDB;
CREATE DATABASE PMSDB;

USE PMSDB;

CREATE TABLE STUDENT(
  ID BIGINT(20) PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
  NAME VARCHAR(200) COMMENT '姓名',
  AGE INT COMMENT '年龄'
) ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=10000 COMMENT='学生信息';

INSERT INTO STUDENT (NAME, AGE) VALUES ('张三', 23);
INSERT INTO STUDENT (NAME, AGE) VALUES ('李四', 24);
INSERT INTO STUDENT (NAME, AGE) VALUES ('王五', 25);
INSERT INTO STUDENT (NAME, AGE) VALUES ('赵六', 26);
INSERT INTO STUDENT (NAME, AGE) VALUES ('钱七', 27);

查看

[root@localhost /]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| PMSDB              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use PMSDB
Database changed
mysql> show tables;
+-----------------+
| Tables_in_PMSDB |
+-----------------+
| STUDENT         |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM STUDENT;
+-------+--------+------+
| ID    | NAME   | AGE  |
+-------+--------+------+
| 10000 | 张三   |   23 |
| 10001 | 李四   |   24 |
| 10002 | 王五   |   25 |
| 10003 | 赵六   |   26 |
| 10004 | 钱七   |   27 |
+-------+--------+------+
5 rows in set (0.00 sec)

mysql> exit
Bye

安装 xtrabackup

在 master 和 slave 中安装 xtrabackup

[root@localhost /]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
[root@localhost /]# yum install -y percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
[root@localhost /]# rpm -qa |grep xtrabackup
percona-xtrabackup-24-2.4.9-1.el7.x86_64

备份恢复

master 进行备份，备份时启动 MySQL；slave 进行恢复，恢复前关闭 MySQL，并且清空 slave 的数据目录。

master 备份

创建备份目录

[root@localhost /]# mkdir -p /data/backup

授予 mysql 权限

[root@localhost /]# chown -R mysql:mysql /data/backup/

切换到 mysql 用户，通过 xtrabackup 进行全量备份

# 备份全部数据库
# --defaults-file=/etc/my.cnf 指定的备份数据的配置文件
# --databases="mysql PMSDB" 指定要备份的数据库
# --safe-slave-backup 该选项表示为保证一致性复制状态，这个选项停止SQL线程并且等到show status中的slave_open_temp_tables为0的时候开始备份，如果没有打开临时表，bakcup会立刻开始，否则SQL线程启动或者关闭直到没有打开的临时表。如果slave_open_temp_tables在--safe-slave-backup-timeount（默认300秒）秒之后不为0，从库sql线程会在备份完成的时候重启
# --user 数据库用户名
# --password 数据库密码
# /data/backup/ 备份目标目录
[mysql@localhost /]$ /usr/bin/innobackupex --defaults-file=/etc/my.cnf --safe-slave-backup --user=root --password=123456 --socket=/tmp/mysql.sock /data/backup/
210428 09:24:56 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

210428 09:24:56  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
210428 09:24:56  version_check Connected to MySQL server
210428 09:24:56  version_check Executing a version check against the server...
210428 09:24:56  version_check Done.
210428 09:24:56 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /tmp/mysql.sock
Using server version 5.7.34
/usr/bin/innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
210428 09:24:56 >> log scanned up to (2980963)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
210428 09:24:57 [01] Copying ./ibdata1 to /data/backup/2021-04-28_09-24-56/ibdata1
210428 09:24:57 [01]        ...done
......
......
......
210428 09:24:59 [01] Copying ./PMSDB/STUDENT.frm to /data/backup/2021-04-28_09-24-56/PMSDB/STUDENT.frm
210428 09:24:59 [01]        ...done
210428 09:24:59 Finished backing up non-InnoDB tables and files
210428 09:24:59 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2980954'
xtrabackup: Stopping log copying thread.
.210428 09:24:59 >> log scanned up to (2980963)

210428 09:24:59 Executing UNLOCK TABLES
210428 09:24:59 All tables unlocked
210428 09:24:59 [00] Copying ib_buffer_pool to /data/backup/2021-04-28_09-24-56/ib_buffer_pool
210428 09:24:59 [00]        ...done
210428 09:24:59 Backup created in directory '/data/backup/2021-04-28_09-24-56/'
210428 09:24:59 [00] Writing /data/backup/2021-04-28_09-24-56/backup-my.cnf
210428 09:24:59 [00]        ...done
210428 09:24:59 [00] Writing /data/backup/2021-04-28_09-24-56/xtrabackup_info
210428 09:24:59 [00]        ...done
xtrabackup: Transaction log of lsn (2980954) to (2980963) was copied.
210428 09:24:59 completed OK!

进入备份目标目录查看备份结果

[mysql@localhost /]$ cd /data/backup/2021-04-28_09-24-56/
[mysql@localhost 2021-04-28_09-24-56]$ ll
total 12336
-rw-r-----. 1 mysql mysql      424 Apr 28 09:24 backup-my.cnf
-rw-r-----. 1 mysql mysql      436 Apr 28 09:24 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Apr 28 09:24 ibdata1
drwxr-x---. 2 mysql mysql     4096 Apr 28 09:24 mysql
drwxr-x---. 2 mysql mysql     8192 Apr 28 09:24 performance_schema
drwxr-x---. 2 mysql mysql       58 Apr 28 09:24 PMSDB
drwxr-x---. 2 mysql mysql     8192 Apr 28 09:24 sys
-rw-r-----. 1 mysql mysql      113 Apr 28 09:24 xtrabackup_checkpoints
-rw-r-----. 1 mysql mysql      495 Apr 28 09:24 xtrabackup_info
-rw-r-----. 1 mysql mysql     2560 Apr 28 09:24 xtrabackup_logfile
[mysql@localhost 2021-04-28_09-24-56]$

将 /data/backup/2021-04-28_09-24-56 传至 slave

[root@localhost /]# scp -r /data/backup/2021-04-28_09-24-56 mysql@192.168.23.131:/data/backup/
mysql@192.168.23.131's password:

slave 恢复（管理员用户登录）

停止 MySQL 服务

[root@localhost /]# service mysql stop
Shutting down MySQL.. SUCCESS! 
[root@localhost /]#

备份 slave 数据库的数据文件

[root@localhost /]# mv /data/mysql /data/backup/local/20210428

删除 slave 的原有数据文件

[root@localhost mysql]# cd /
[root@localhost /]# rm -rf /data/mysql/

配置 slave 的数据目录路径，必须和 master 相同

[root@localhost mysql]# more /etc/my.cnf
[mysqld]
datadir=/data/mysql

应用备份文件，回滚未提交的事务

# --apply-log 该选项表示同 xtrabackup 的 --prepare 参数,一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。--apply-log 的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
# --use-memory 该选项表示和 --apply-log 选项一起使用，prepare 备份的时候，xtrabackup 做 crash recovery 分配的内存大小，单位字节。也可(1MB,1M,1G,1GB)，推荐 1G。
[root@localhost /]# /usr/bin/innobackupex --apply-log --use-memory=1G /data/backup/2021-04-28_09-24-56
210428 09:34:36 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

/usr/bin/innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: cd to /data/backup/2021-04-28_09-24-56/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2980954)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 1073741824 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 1G, instances = 1, chunk size = 128M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2980954
InnoDB: Doing recovery: scanned up to log sequence number 2980963 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 2980963 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.13 started; log sequence number 2980963

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2980982
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 1G, instances = 1, chunk size = 128M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=2980982
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2981388
InnoDB: Doing recovery: scanned up to log sequence number 2981397 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 2981397 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.13 started; log sequence number 2981397
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2981416
210428 09:34:41 completed OK!
[root@localhost /]#

执行恢复操作

[root@localhost /]# /usr/bin/innobackupex --defaluts-file=/etc/my.cnf --copy-back /data/backup/2021-04-28_09-24-56/
210428 10:33:54 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

/usr/bin/innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
......
......
......
210428 10:33:56 [01] Copying ./xtrabackup_info to /data/mysql/xtrabackup_info
210428 10:33:56 [01]        ...done
210428 10:33:56 [01] Copying ./ibtmp1 to /data/mysql/ibtmp1
210428 10:33:56 [01]        ...done
210428 10:33:56 completed OK!

修改组和用户

[root@localhost /]# chown -R mysql:mysql /data/mysql/

启动MySQL服务

[root@localhost /]# service mysql start
Starting MySQL.Logging to '/data/mysql/mysql.err'.
.. SUCCESS! 
[root@localhost /]#

查询

[root@localhost /]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# 查询所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| PMSDB              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

# 选择 PMSDB 数据库
mysql> use PMSDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

# 查询所有表
mysql> show tables;
+-----------------+
| Tables_in_PMSDB |
+-----------------+
| STUDENT         |
+-----------------+
1 row in set (0.00 sec)

# 查询 STUDENT 表数据
mysql> SELECT * FROM STUDENT;
+-------+--------+------+
| ID    | NAME   | AGE  |
+-------+--------+------+
| 10000 | 张三   |   23 |
| 10001 | 李四   |   24 |
| 10002 | 王五   |   25 |
| 10003 | 赵六   |   26 |
| 10004 | 钱七   |   27 |
+-------+--------+------+
5 rows in set (0.00 sec)

# 插入 STUDENT
mysql> INSERT INTO STUDENT (NAME, AGE) VALUES ('ZHANGBA', 28);
Query OK, 1 row affected (0.00 sec)

# 再次查询 STUDENT 表
mysql> SELECT * FROM STUDENT;
+-------+---------+------+
| ID    | NAME    | AGE  |
+-------+---------+------+
| 10000 | 张三    |   23 |
| 10001 | 李四    |   24 |
| 10002 | 王五    |   25 |
| 10003 | 赵六    |   26 |
| 10004 | 钱七    |   27 |
| 10005 | ZHANGBA |   28 |
+-------+---------+------+
6 rows in set (0.00 sec)

查看MySQL的配置信息

查看 log_bin

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

转载自:https://juejin.cn/post/7013923422702927886

Linux 系统安装 MySql 5.7 并测试进行数据库迁移

应用背景

环境介绍

服务器版本

两台 Cent OS 7 服务器：master，slave，各有如下两个用户

数据库版本

mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

迁移工具

Xtrabackup工具（开源免费）

安装MySQL5.7

登录服务器

上传 mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz，查看

解压

切换管理员用户

创建组和用户

创建数据目录并修改权限

配置 my.cnf

初始化数据库

查看临时密码

启动MySQL

登录MySQL

修改密码

添加远程访问权限

最后，使用 客户端 远程访问即可

防火墙开放 3306 端口

建立MySQL链接文件

开机自启

master中创建数据库和表

创建数据库和表

查看

安装 xtrabackup

在 master 和 slave 中安装 xtrabackup

备份恢复

master 备份

创建备份目录

授予 mysql 权限

切换到 mysql 用户，通过 xtrabackup 进行全量备份

进入备份目标目录查看备份结果

将 /data/backup/2021-04-28_09-24-56 传至 slave

slave 恢复（管理员用户登录）

停止 MySQL 服务

备份 slave 数据库的数据文件

删除 slave 的原有数据文件

配置 slave 的数据目录路径，必须和 master 相同

应用备份文件，回滚未提交的事务

执行恢复操作

修改组和用户

启动MySQL服务

查询

查看MySQL的配置信息

查看 log_bin

最后，使用客户端远程访问即可