运用布景

此次为配合行内对现有 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)