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