十五周二次课

青旅半醒 2022-01-13 12:45 196阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

十五周二次课

17.1mysql主从介绍

17.2准备工作

17.3配置主

17.4配置从

17.5测试主从同步

17.1mysql主从介绍

## MySQL主从介绍 ##

*  MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的
 *  MySQL主从是基于binlog的，主上须开启binlog才能进行主从。
    
     *  binlog，其实就是一个文件，文件里记录了一些日志，文件是 二进制文件，无法cat
 *  主从过程大致有3个步骤
    
     *  1）主将更改操作记录到binlog里
     *  2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里
        
         *  relaylog，中文叫做 中继日志
     *  3）从根据relaylog里面的sql语句按顺序执行

*  mysql主从共有三个线程
    
     *  主上有一个log dump线程，用来和从的I/O线程传递binlog
     *  从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地

### MySQL主从原理图 ###

![输入图片说明][23082813_cVDJ.png]

原理很简单：从会把主上的binlog搞到从上来，从再根据这个binlog生成自己的中继日志，然后再根据中继日志执行相应的更改，最终达到两边的数据一致。

### mysql主从使用场景： ###

1.  数据备份，主机器宕机，从机器还能随时对web提供服务；
2.  作为一个从库，读的库，减轻主库的压力，数据备份且可以分担主机器被调用数据时的压力，mysql主从，是有方向性的，写数据，必须从主机器开始，如果不依照原理会导致数据紊乱。

17.2准备工作

## mysql安装总结 ##

*  mysql主从准备工作：
    
     *  准备两台机器，每台机器安装msyql服务，并启动mysql服务
 *  [mysql详细安装][mysql]

1.首先下载二进制免编译的包，下载到/usr/local/src/目录下

2.解压压缩包

3.解压完之后，把解压出来的目录放到 /usr/local/mysql/ 目录下

*  注意点：
    
     *  首先检查 /usr/local/mysql/ 目录是否存在
     *  若是这个目录存在，首先把这个目录改个名字，或者把目录下的内容删除
     *  然后把解压出来的目录放到 /usr/local/mysql/ 目录下面
 *  目录内容应该如下

[root@hanfeng ~]# ls /usr/local/mysql
    bin      data  include  man     my-new.cnf  README   share      support-files
    COPYING  docs  lib      my.cnf  mysql-test  scripts  sql-bench
    [root@hanfeng ~]#

4.然后切换到 /usr/local/mysql/ 目录下，进行初始化 命令

*  初始化命令 ./scripts/mysql\_install\_db --user=mysql --datadir=/data/mysql
    
     *  注意点：
        
         *  其中的--user=mysql 需要提前创建

5.初始化成功的标志就是两个OK，或者用 echo $? 检查是否初始化成功

6.编辑 /etc/my.cnf 文件——>默认是自带 my.cnf 文件的

在 /etc/my.cnf 文件中
    定义 datadir=/data/mysql     
    定义 socket=/tmp/mysql.sock

7.拷贝启动脚本

*  命令 cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

8.编辑启动脚本

vim /etc/init.d/mysqld //对以下两行进行指定路径
    
    指定basedir的路径 /usr/local/mysql  
    指定datadir的路径 /data/mysql

9.之后就可以启动mysql了

*  命令 /etc/init.d/mysql start

10.如果启动失败，可以去查看错误日志

11.建议 ：

*  查看 /data/mysql 目录下的文件，默认属主、属组，如果不是mysql的，启动时会因无法写入数据而不能启动mysql
    
     *  改变属主和属组，命令 chomd mysql:mysql /data/mysql
 *  然后就可以尝试启动，命令 /etc/init.d/mysql start

12.若想开机启动，只需要输入命令

*  命令 chkconfig mysqld on

# 17.3 配置主 #

# 主从配置 - 主上操作 #

*  安装mysql
 *  修改my.cnf，增加server-id=130和log\_bin=aminglinux1
 *  修改完配置文件后，启动或者重启mysqld服务
 *  把mysql库备份并恢复成aming库，作为测试数据
 *  mysqldump -uroot mysql > /tmp/mysql.sql
 *  mysql -uroot -e “create database aming”
 *  mysql -uroot aming < /tmp/mysql.sql
 *  创建用作同步数据的用户
 *  grant replication slave on *.* to 'repl'@slave\_ip identified by 'password';
 *  flush tables with read lock;
 *  show master status;

## 主从配置 - 主上操作 ##

1.在两台机器安装并启动mysql服务后，首先在主上进行操作（主为192.168.23.136，从为192.168.23.130）。

2.修改/etc/my.cnf文件

*  在配置文件中\[mysqld\]下添加
    
     *  server-id=136,这个id可以自定义，这里根据ip来定义
     *  log\_bin=aminglinux1,打开binlog，名字自定义为aminglinux1,最终结果 如下：

\[root[@localhost][localhost] ~\]\# vim /etc/my.cnf

\[mysqld\]  
datadir=/data/mysql  
socket=/tmp/mysql.sock  
server-id=136  
log\_bin=aminglinux1  
\# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0  
\# Settings user and group are ignored when systemd is used.  
\# If you need to run mysqld under a different user or group,  
\# customize your systemd unit file for mariadb according to the  
\# instructions in http://fedoraproject.org/wiki/Systemd

\[mysqld\_safe\]  
\#log-error=/var/log/mariadb/mariadb.log  
\#pid-file=/var/run/mariadb/mariadb.pid

\#  
\# include all files from the config directory  
\#  
\#!includedir /etc/my.cnf.d

保存退出

3.更改完配置以后，需要重启mysql服务。

\[root[@localhost][localhost] ~\]\# /etc/init.d/mysqld restart  
Shutting down MySQL... SUCCESS!  
Starting MySQL.......... SUCCESS!  
\[root[@localhost][localhost] ~\]\#

如果重启过程中出现“Plugin 'FEDERATED' is disabled”错误，删除/data目录下除mysql以外的目录即可

4.查看/data/mysql目录下，会生成一些文件

\[root[@localhost][localhost] mysql\]\# ls -lt /data/mysql  
total 110656  
\-rw-rw---- 1 mysql mysql 120 Jun 17 14:23 aminglinux1.000001  
\-rw-rw---- 1 mysql mysql 21 Jun 17 14:23 aminglinux1.index  
\-rw-rw---- 1 mysql mysql 56 Jun 5 10:09 auto.cnf  
drwxr-xr-x 2 root root 6 Jun 17 14:31 blog  
\-rw-rw---- 1 mysql mysql 12582912 Jun 17 14:23 ibdata1  
\-rw-rw---- 1 mysql mysql 50331648 Jun 17 14:23 ib\_logfile0  
\-rw-rw---- 1 mysql mysql 50331648 Jun 5 10:08 ib\_logfile1  
\-rw-rw---- 1 mysql mysql 36693 Jun 17 14:23 localhost.localdomain.err  
\-rw-rw---- 1 mysql mysql 5 Jun 17 14:23 localhost.localdomain.pid  
drwx------ 2 mysql mysql 4096 Jun 5 10:08 mysql  
drwx------ 2 mysql mysql 4096 Jun 17 11:08 mysql2  
drwx------ 2 mysql mysql 4096 Jun 5 10:08 performance\_schema  
drwx------ 2 mysql mysql 6 Jun 5 10:08 test  
\[root@localhost mysql\]\#

*  其中，.index索引页这个文件是必需的
 *  .000001文件是个二进制文件，会持续生成2，3，4等等（这个文件是实现主从复制的根本，没有这个文件根本没法完成主从）

5.测试，准备做一个数据做演示看

6.首先做一个备份

\[root@localhost mysql\]\# mysqldump -uroot -paminglinux blog > /tmp/blog.sql  
Warning: Using a password on the command line interface can be insecure.  
\[root@localhost mysql\]\#

7.创建一个新的数据库

\[root@localhost mysql\]\# mysql -uroot -paminglinux -e "create database aming"  
Warning: Using a password on the command line interface can be insecure.  
\[root@localhost mysql\]\#

8.创建好数据库以后，还需要把数据恢复一下，也就是说做的主从，参考对象是aming数据库

\[root@localhost mysql\]\# mysql -uroot -paminglinux aming < /tmp/blog.sql   
Warning: Using a password on the command line interface can be insecure.  
\[root@localhost mysql\]\#

9.再次查看/data/mysql目录下的文件

\[root@localhost mysql\]\# ls -l /data/mysql  
total 110656  
drwx------ 2 mysql mysql 20 Jun 17 14:33 aming  
\-rw-rw---- 1 mysql mysql 217 Jun 17 14:33 aminglinux1.000001  
\-rw-rw---- 1 mysql mysql 21 Jun 17 14:23 aminglinux1.index  
\-rw-rw---- 1 mysql mysql 56 Jun 5 10:09 auto.cnf  
drwxr-xr-x 2 root root 6 Jun 17 14:31 blog  
\-rw-rw---- 1 mysql mysql 12582912 Jun 17 14:23 ibdata1  
\-rw-rw---- 1 mysql mysql 50331648 Jun 17 14:23 ib\_logfile0  
\-rw-rw---- 1 mysql mysql 50331648 Jun 5 10:08 ib\_logfile1  
\-rw-rw---- 1 mysql mysql 36693 Jun 17 14:23 localhost.localdomain.err  
\-rw-rw---- 1 mysql mysql 5 Jun 17 14:23 localhost.localdomain.pid  
drwx------ 2 mysql mysql 4096 Jun 5 10:08 mysql  
drwx------ 2 mysql mysql 4096 Jun 17 11:08 mysql2  
drwx------ 2 mysql mysql 4096 Jun 5 10:08 performance\_schema  
drwx------ 2 mysql mysql 6 Jun 5 10:08 test  
\[root@localhost mysql\]\#

10.正常情况下，能看到aminglinux1.000001二进制文件是由增加的，增加的大小是和blog库保持一致的，aminglinux1.000001完整地记录了数据库的过程。创建的库，创建的表，以及表里的内容全都有。

11.下面创建主从相互同步用的用户

12.进入mysql里面去

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

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>

13.创建用户

*  grant制定权限，replication slave权限
 *  针对repl这个用户
 *  针对从的ip制定来源（若是写所有的ip会很危险）

mysql> grant replication slave on \*.\* to 'repl'@'192.168.23.130' identified by 'aminglinux111';  
Query OK, 0 rows affected (0.00 sec)

mysql>

14.锁定表，目的是不让表继续写了，数据到此暂停，先把这个状态保持在这儿。因为一会儿要做从的机器配置，需要两台机器同步，保证两台机器的数据一致，同步才不会出错

mysql> flush tables with read lock;  
Query OK, 0 rows affected (0.10 sec)

mysql>

15.查看一下binlog文件的大小，记住名字和位置

mysql>

16.然后退出数据库，做一个数据同步

17.查看/data/mysql下都有哪些库，主上有哪些库，从上就要同步哪些库，意味着数据都有备份过去

\[root@localhost mysql\]\# ls  
aming aminglinux1.index blog ib\_logfile0 localhost.localdomain.err mysql performance\_schema  
aminglinux1.000001 auto.cnf ibdata1 ib\_logfile1 localhost.localdomain.pid mysql2 test blog  
\[root@localhost mysql\]\#

18.备份数据库，除了mysql库，因为mysql库里面有账号密码，从上的时候不可能把所有权限复制过去，所以mysql不需要备份

\[root@localhost mysql\]\# mysqldump -uroot -paminglinux mysql2 > /tmp/my2.sql  
Warning: Using a password on the command line interface can be insecure.  
\[root@localhost mysql\]\# mysqldump -uroot -paminglinux zrlog > /tmp/zrlog.sql  
Warning: Using a password on the command line interface can be insecure.  
\[root@localhost mysql\]\#

19.一会儿把/tmp/下.sql文件都拷贝到从上去

\[root@localhost mysql\]\# ls /tmp/\*.sql  
/tmp/blog.sql /tmp/my2.sql /tmp/mysql2.sql /tmp/mysql\_all.sql /tmp/mysqlbak.sql /tmp/user.sql /tmp/zrlog.sql

20.主上操作完成，接下来从上操作

# 17.4 配置从 #

## 主从配置 - 从上操作 ##

*  安装mysql
 *  查看my.cnf，配置server-id=132，要求和主不一样
 *  修改完配置文件后，启动或者重启mysqld服务
 *  把主上aming库同步到从上
 *  可以先创建aming库，然后把主上的/tmp/mysql.sql拷贝到从上，然后导入aming库
 *  mysql -uroot
 *  stop slave；
 *  change master to master\_host='', master\_user='repl', master\_password='', master\_log\_file='', master\_log\_pos=xx,
 *  start slave;
 *  还要到主上执行 unlock tables

## 主从配置 - 从上操作 ##

1.首先在从上安装并启动mysql，然后查看my.cnf，配置server-id=130，要求和主不一样，在配置文件的log\_bin参数就不需要配置的，因为只有主上才需要二进制日志文件

\[root@tianqi-01 ~\]\# vim /etc/my.cnf

\[mysqld\]  
character\_set\_server = utf8  
datadir=/data/mysql  
socket=/tmp/mysql.sock  
server-id=130  
log\_bin=aminglinux1  
\# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0  
\# Settings user and group are ignored when systemd is used.  
\# If you need to run mysqld under a different user or group,  
\# customize your systemd unit file for mariadb according to the  
\# instructions in http://fedoraproject.org/wiki/Systemd

\[mysqld\_safe\]  
\#log-error=/var/log/mariadb/mariadb.log  
\#pid-file=/var/run/mariadb/mariadb.pid

\#  
\# include all files from the config directory  
\#  
\#!includedir /etc/my.cnf.d

2.重启mysql服务

\[root@tianqi-01 ~\]\# /etc/init.d/mysqld restart  
Shutting down MySQL....... \[ 确定 \]  
Starting MySQL...................... \[ 确定 \]  
\[root@tianqi-01 ~\]\#

3.增加server-id后，对mysql是没有变化的

\[root@tianqi-01 ~\]\# ls /data/mysql  
aming aminglinux1.000007 aminglinux1.000014 aminglinux1.000021 aminglinux1.000028 ib\_logfile0 tianqi-01.pid  
aminglinux1.000001 aminglinux1.000008 aminglinux1.000015 aminglinux1.000022 aminglinux1.000029 ib\_logfile1 zabbix  
aminglinux1.000002 aminglinux1.000009 aminglinux1.000016 aminglinux1.000023 aminglinux1.000030 mysql zrlog  
aminglinux1.000003 aminglinux1.000010 aminglinux1.000017 aminglinux1.000024 aminglinux1.index mysql2  
aminglinux1.000004 aminglinux1.000011 aminglinux1.000018 aminglinux1.000025 auto.cnf performance\_schema  
aminglinux1.000005 aminglinux1.000012 aminglinux1.000019 aminglinux1.000026 db1 test  
aminglinux1.000006 aminglinux1.000013 aminglinux1.000020 aminglinux1.000027 ibdata1 tianqi-01.err  
\[root@tianqi-01 ~\]\#

4.把主机器上备份的 .sql 数据，拷贝到从机器上，然后做一个数据恢复

\[root@tianqi-01 ~\]\# scp 192.168.23.136:/tmp/\*.sql /tmp/  
The authenticity of host '192.168.23.136 (192.168.23.136)' can't be established.  
ECDSA key fingerprint is SHA256:A62fDLxjGpEeD/g4UPe/2LtbDTOkiS9zpWjO7w9tZGc.  
ECDSA key fingerprint is MD5:04:16:78:f6:50:ad:0a:cf:60:57:b2:b0:cb:2e:f8:4c.  
Are you sure you want to continue connecting (yes/no)? yes  
Warning: Permanently added '192.168.23.136' (ECDSA) to the list of known hosts.  
root@192.168.23.136's password:  
blog.sql 100% 1258 1.1MB/s 00:00  
my2.sql 100% 638KB 3.7MB/s 00:00  
mysql2.sql 100% 30KB 1.8MB/s 00:00  
mysql\_all.sql 100% 1276KB 2.7MB/s 00:00  
mysqlbak.sql 100% 638KB 9.0MB/s 00:00  
user.sql 100% 7026 5.8MB/s 00:00  
zrlog.sql 100% 1259 950.7KB/s 00:00  
\[root@tianqi-01 ~\]\#

5.创建数据库

mysql> create database aming;  
Query OK, 1 row affected (0.00 sec)

mysql> create database zrlog;  
Query OK, 1 row affected (0.00 sec)

mysql> create database blog;  
Query OK, 1 row affected (0.00 sec)

mysql> create database mysql2;  
Query OK, 1 row affected (0.00 sec)

mysql>

6.然后将数据做一个恢复

\[root@tianqi-01 ~\]\# mysql -uroot -paminglinux blog < /tmp/blog.sql   
Warning: Using a password on the command line interface can be insecure.  
\[root@tianqi-01 ~\]\# mysql -uroot -paminglinux zrlog < /tmp/zrlog.sql   
Warning: Using a password on the command line interface can be insecure.  
\[root@tianqi-01 ~\]\# mysql -uroot -paminglinux mysql2 < /tmp/mysql2.sql   
Warning: Using a password on the command line interface can be insecure.  
\[root@tianqi-01 ~\]\# mysql -uroot -paminglinux aming < /tmp/blog.sql   
Warning: Using a password on the command line interface can be insecure.  
\[root@tianqi-01 ~\]\#

7.然后查看/data/mysql/目录下的数据是否和主机器上的/data/mysql/目录是否一致

8.开始实现主从

9.在从机器登录到mysql

\[root@tianqi-01 ~\]\# mysql -uroot -p  
Enter password:  
Welcome to the MySQL monitor. Commands end with ; or \\g.  
Your MySQL connection id is 31  
Server version: 5.6.35-log MySQL Community Server (GPL)

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>

10.然后在数据库里面执行命令，停止slave

mysql> stop slave;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

11.进行主机器相关配置

*  change master to master\_host='192.168.23.136', master\_user='repl', master\_password='aminglinux111', master\_log\_file='aminglinux1.000001', master\_log\_pos=428
    
     *  master\_host='192.168.23.136'，指定主机期host
     *  master\_user='repl'，指定主机器用户
     *  master\_password='aminglinux111'，指定主机器密码
     *  master\_log\_file='aminglinux1.000001'，指定binlog文件名
     *  master\_log\_pos=428，指定binlog文件大小
 *  也可以指定主机器的port，因为在生产环境中，也会有人更改mysql的默认端口 master\_port=3306

mysql> change master to master\_host='192.168.23.136', master\_user='repl', master\_password='aminglinux111', master\_log\_file='aminglinux1.000001', master\_log\_pos=428;  
Query OK, 0 rows affected, 2 warnings (0.13 sec)

mysql>

12.开始slave

mysql> start slave;  
Query OK, 0 rows affected (0.02 sec)

mysql>

13.这时候通过 show slave status\\G 判断主从是否配置成功

*  show slave status\\G
    
     *  在G后面不需要加分号，\\G本身就是一种结束符
     *  看 Slave\_IO\_Running: Yes 是否为yes
     *  看 Slave\_SQL\_Running: Yes 是否为yes

mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Slave\_IO\_State: Waiting for master to send event  
Master\_Host: 192.168.23.136  
Master\_User: repl  
Master\_Port: 3306  
Connect\_Retry: 60  
Master\_Log\_File: aminglinux1.000001  
Read\_Master\_Log\_Pos: 428  
Relay\_Log\_File: tianqi-01-relay-bin.000002  
Relay\_Log\_Pos: 285  
Relay\_Master\_Log\_File: aminglinux1.000001  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
Replicate\_Do\_DB:  
Replicate\_Ignore\_DB:  
Replicate\_Do\_Table:  
Replicate\_Ignore\_Table:  
Replicate\_Wild\_Do\_Table:  
Replicate\_Wild\_Ignore\_Table:  
Last\_Errno: 0  
Last\_Error:  
Skip\_Counter: 0  
Exec\_Master\_Log\_Pos: 428  
Relay\_Log\_Space: 462  
Until\_Condition: None  
Until\_Log\_File:  
Until\_Log\_Pos: 0  
Master\_SSL\_Allowed: No  
Master\_SSL\_CA\_File:  
Master\_SSL\_CA\_Path:  
Master\_SSL\_Cert:  
Master\_SSL\_Cipher:  
Master\_SSL\_Key:  
Seconds\_Behind\_Master: 0  
Master\_SSL\_Verify\_Server\_Cert: No  
Last\_IO\_Errno: 0  
Last\_IO\_Error:  
Last\_SQL\_Errno: 0  
Last\_SQL\_Error:  
Replicate\_Ignore\_Server\_Ids:  
Master\_Server\_Id: 136  
Master\_UUID: 8c4ee77b-6865-11e8-8647-000c2970f861  
Master\_Info\_File: /data/mysql/master.info  
SQL\_Delay: 0  
SQL\_Remaining\_Delay: NULL  
Slave\_SQL\_Running\_State: Slave has read all relay log; waiting for the slave I/O thread to update it  
Master\_Retry\_Count: 86400  
Master\_Bind:  
Last\_IO\_Error\_Timestamp:  
Last\_SQL\_Error\_Timestamp:  
Master\_SSL\_Crl:  
Master\_SSL\_Crlpath:  
Retrieved\_Gtid\_Set:  
Executed\_Gtid\_Set:  
Auto\_Position: 0  
1 row in set (0.01 sec)

mysql>

注意：这里可能会出现**Slave\_IO\_Running: Connecting**错误，主要原因有三个：1、网络不通2、密码不对3、pos不对，仔细检查发现这3项没有错误之后，检查一下主的防火墙是或关闭

14.解锁主上的表

mysql> unlock tables;  
Query OK, 0 rows affected (0.09 sec)

15.到这里主从搭建就算完成了

## 查看主从同步是否正常 ##

*  从上执行mysql -uroot
 *  show slave stauts\\G
 *  看是否有
 *  Slave\_IO\_Running: Yes
 *  Slave\_SQL\_Running: Yes
 *  还需关注
 *  Seconds\_Behind\_Master: 0 //为主从延迟的时间
 *  Last\_IO\_Errno: 0
 *  Last\_IO\_Error:
 *  Last\_SQL\_Errno: 0
 *  Last\_SQL\_Error:

# 17.5 测试主从同步 #

1.主进入MySQL，使用aming数据库

\[root@localhost ~\]\# mysql -uroot -p  
Enter password:  
Welcome to the MySQL monitor. Commands end with ; or \\g.  
Your MySQL connection id is 11  
Server version: 5.6.35-log MySQL Community Server (GPL)

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> use aming;  
Database changed

2.创建数据表t1

mysql> create table t1(\`id\` int(4),\`name\` char(20));  
Query OK, 0 rows affected (0.66 sec)

3.查看数据表t1

mysql> show tables;  
\+-----------------+  
| Tables\_in\_aming |  
\+-----------------+  
| t1 |  
\+-----------------+  
1 row in set (0.00 sec)

mysql> show create table t1\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Table: t1  
Create Table: CREATE TABLE \`t1\` (  
\`id\` int(4) DEFAULT NULL,  
\`name\` char(20) DEFAULT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=latin1  
1 row in set (0.10 sec)

mysql>

4.从上 进入MySQL，使用aming数据库

\[root@tianqi-01 ~\]\# mysql -uroot -p  
Enter password:  
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)  
\[root@tianqi-01 ~\]\# mysql -uroot -p  
Enter password:  
Welcome to the MySQL monitor. Commands end with ; or \\g.  
Your MySQL connection id is 2092  
Server version: 5.6.35-log MySQL Community Server (GPL)

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> use aming;  
Database changed  
5.查看所有数据表

mysql> show tables;  
\+-----------------+  
| Tables\_in\_aming |  
\+-----------------+  
| t1 |  
\+-----------------+  
1 row in set (0.00 sec)

6.查看数据表t1的建表语句，结果与主的一致，说明主从一直

mysql> show create table t1;  
\+-------+---------------------------------------------------------------------------------------------------------------------+  
| Table | Create Table |  
\+-------+---------------------------------------------------------------------------------------------------------------------+  
| t1 | CREATE TABLE \`t1\` (  
\`id\` int(4) DEFAULT NULL,  
\`name\` char(20) DEFAULT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |  
\+-------+---------------------------------------------------------------------------------------------------------------------+  
1 row in set (0.20 sec)

mysql> show create table t1\\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Table: t1  
Create Table: CREATE TABLE \`t1\` (  
\`id\` int(4) DEFAULT NULL,  
\`name\` char(20) DEFAULT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=utf8  
1 row in set (0.00 sec)

ERROR:  
No query specified

mysql>

转载于:https://my.oschina.net/u/3744518/blog/1831590

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220113/df3d30e135774de29161a0c26b742107.png
[23082813_cVDJ.png]: https://static.oschina.net/uploads/img/201801/23082813_cVDJ.png
[mysql]: https://my.oschina.net/u/3707314/blog/1587228
[localhost]: https://my.oschina.net/u/570656