第 1 章 安装
1.1 MySQL5.7源码编译安装
环境:
操作系统:CentOS6.9, x86_64
1.1.1 MySQL源码包下载
社区版MySQL下载地址:
https://dev.mysql.com/downloads/mysql/
采用源码编译安装:
下载框中的版本。
下载时不用登录:
1.1.2 安装编译工具以及依赖包
卸载系统自带的mysql包:
[root@SSCVL0436 ~]# rpm -qa|grep -i mysql
mysql-libs-5.1.73-8.el6_8.x86_64
[root@SSCVL0436 ~]# rpm -emysql-libs-5.1.73-8.el6_8.x86_64
error: Failed dependencies:
libmysqlclient.so.16()(64bit) is needed by (installed)postfix-2:2.6.6-8.el6.x86_64
libmysqlclient.so.16(libmysqlclient_16)(64bit) is needed by (installed)postfix-2:2.6.6-8.el6.x86_64
mysql-libs is needed by (installed) postfix-2:2.6.6-8.el6.x86_64
可以加上--nodeps选项单独删除mysql-libs:
rpm -e mysql-libs-5.1.73-8.el6_8.x86_64 --nodeps
注意事项:
这里采用源码编译安装,mysql-libs不卸载也没关系。
我并不打算将参数文件放在/etc目录下,所以,该目录下的my.cnf文件需要删除或重命名,以免干扰后续MySQLserver的启动。
安装编译工具以及依赖包:
yum -y install bison cmake gcc gcc-c++ libaio-develncurses ncurses-devel perl
安装后检查:
rpm -q bison \
cmake \
gcc \
gcc-c++ \
libaio-devel \
ncurses \
ncurses-devel \
perl
1.1.3 创建mysql账户
groupadd mysql
useradd -g mysql mysql
passwd mysql
配置mysql账户的系统资源使用限制(可选):
cat >> /etc/security/limits.conf << EOF
mysql soft nproc 2047
mysql hard nproc 16384
mysql soft nofile 1024
mysql hard nofile 65536
EOF
配置mysql账户的环境变量
参考后续MySQL软件安装路径,配置PATH环境变量为:
exportPATH=/data/mysql/bin:$PATH
1.1.4 编译、安装
安装介质所在目录:
[root@SSCVL0436 soft]# pwd
/data/soft
[root@SSCVL0436 soft]# ll
total 47788
-rw-r--r-- 1 root root 48931457 Apr 1512:06 mysql-boost-5.7.21.tar.gz
解压:
[root@SSCVL0436 soft]# tar-xzf mysql-boost-5.7.21.tar.gz
[root@SSCVL0436 soft]# ll
total 47792
drwxr-xr-x 36 7161 31415 4096 Dec 28 11:56 mysql-5.7.21
-rw-r--r-- 1 root root 48931457 Apr 15 12:06 mysql-boost-5.7.21.tar.gz
[root@SSCVL0436 soft]# chown -R root:root mysql-5.7.21
[root@SSCVL0436 soft]# ll
total 47792
drwxr-xr-x 36 root root 4096 Dec 28 11:56 mysql-5.7.21
-rw-r--r-- 1 root root 48931457 Apr 15 12:06 mysql-boost-5.7.21.tar.gz
创建临时目录bld:
[root@SSCVL0436 soft]# cdmysql-5.7.21
[root@SSCVL0436 mysql-5.7.21]# mkdir bld
[root@SSCVL0436 mysql-5.7.21]# cd bld
执行下述命令生成编译环境:
cmake ..-DCMAKE_INSTALL_PREFIX=/data/mysql \
-DSYSCONFDIR=/data/mysqldata\
-DMYSQL_DATADIR=/data/mysqldata/data\
-DMYSQL_UNIX_ADDR=/data/mysqldata/run/mysql.sock\
-DWITH_BLACKHOLE_STORAGE_ENGINE=1\
-DWITH_EXAMPLE_STORAGE_ENGINE=1\
-DWITH_FEDERATED_STORAGE_ENGINE=1\
-DWITH_PARTITION_STORAGE_ENGINE=1\
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1\
-DWITH_BOOST=../boost\
-DWITH_INNODB_MEMCACHED=ON\
-DENABLED_LOCAL_INFILE=ON\
-DDEFAULT_CHARSET=utf8\
-DDEFAULT_COLLATION=utf8_general_ci\
-DCOMPILATION_COMMENT="MySQLServer (GPL)"
The InnoDB, MyISAM, MERGE, MEMORY, and CSVengines are mandatory (always compiled into the server) and need not beinstalled explicitly.
将软件目录与数据目录区分开来。
编译失败,删除bld目录中的所有内容即可重新编译。
cmake选项说明:
选项 | 说明 |
|
|
CMAKE_INSTALL_PREFIX | 软件安装base目录,如:/usr/local/mysql |
|
|
|
|
执行cmake的过程,就是配置编译环境的过程,执行完毕,结果如图所示:
编译MySQL:
[root@SSCVL0436 bld]# make -j 2
编译结束后的样子,没有报错:
注意:
磁盘空间不能太小,否则会导致编译失败。
[root@SSCVL0436 bld]# du -sh
4.9G .
安装MySQL:
[root@SSCVL0436 bld]# makeinstall
安装完毕的样子,同样没有报错:
检查软件安装目录:
[root@SSCVL0436 bld]# cd /data/mysql
[root@SSCVL0436 mysql]# ll
total 80
drwxr-xr-x 2 root root 4096 Apr 16 10:27 bin
-rw-r--r-- 1 root root 17987 Dec 28 11:46 COPYING
-rw-r--r-- 1 root root 17987 Dec 28 11:46 COPYING-test
drwxr-xr-x 2 root root 4096 Apr 16 10:27 docs
drwxr-xr-x 3 root root 4096 Apr 16 10:27 include
drwxr-xr-x 4 root root 4096 Apr 16 10:27 lib
drwxr-xr-x 4 root root 4096 Apr 16 10:27 man
drwxr-xr-x 10 root root 4096 Apr 16 10:27 mysql-test
-rw-r--r-- 1 root root 2478 Dec 28 11:46 README
-rw-r--r-- 1 root root 2478 Dec 28 11:46 README-test
drwxr-xr-x 28 root root 4096 Apr 16 10:27 share
drwxr-xr-x 2 root root 4096 Apr 16 10:27support-files
注意:
如果安装目录不存在,安装时会自动创建。
1.1.5 建库
修改软件目录权限:
chown -R root:mysql /data/mysql
建立数据目录并修改权限:
mkdir -p/data/mysqldata/{run,data,tmp,binlog,logs}
chown -R mysql:mysql/data/mysqldata
1.1.5.1 创建MySQL参数文件
su - mysql
vi /data/mysqldata/my.cnf
[client]
port = 3306
socket = /data/mysqldata/run/mysql.sock
[mysqld]
#mysql server
server-id = 85
port = 3306
user = mysql
socket =/data/mysqldata/run/mysql.sock
pid-file =/data/mysqldata/run/mysql.pid
basedir = /data/mysql
datadir =/data/mysqldata/data
tmpdir =/data/mysqldata/tmp
default-storage-engine = INNODB
character-set-server = utf8
explicit_defaults_for_timestamp = true
#buffer
max_connections = 1024
max_allowed_packet = 256M
max_heap_table_size = 256M
net_buffer_length = 8K
sort_buffer_size = 2M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
#binlog
log-bin = /data/mysqldata/binlog/mysql-bin
binlog_cache_size = 32M
max_binlog_cache_size = 512M
max_binlog_size = 512M
binlog_format = mixed
expire-logs-days = 14
#error log
log-error = /data/mysqldata/logs/mysql-err.log
#query log
log_output = FILE
slow_query_log = 1
slow_query_log_file = /data/mysqldata/logs/slow_query.log
general_log = 0
general_log_file = /data/mysqldata/logs/general_query.log
#InnoDB
innodb_data_file_path = ibdata1:2048M:autoextend:max:10G
innodb_file_per_table = 1
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_buffer_pool_size = 1024M
transaction_isolation = READ-COMMITTED
[mysqld_safe]
open_files_limit = 10240
[mysql]
default-character-set = utf8
mysqld选项说明:
选项 | 说明 |
|
|
explicit_defaults_for_timestamp | 设置timestamp的列值为null,不会被设置为current timestamp。默认值为false,应该设置为true。 |
|
|
|
|
InnoDB选项说明:
选项 | 说明 |
|
|
innodb_buffer_pool_size | the memory area where InnoDB caches table and index data. When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server. 推荐设置:物理内存的60%~80% |
innodb_buffer_pool_instances | This option only takes effect when setting innodb_buffer_pool_size to 1GB or more. 默认值为8,当innodb_buffer_pool_size小于1GB时,默认值为1。 |
|
|
1.1.5.2 初始建库
[root@SSCVL0436 ~]# /data/mysql/bin/mysqld--defaults-file=/data/mysqldata/my.cnf --initialize --user=mysql--explicit_defaults_for_timestamp
在这里执行下面这个命令,explicit_defaults_for_timestamp选项不用放命令行,加入参数文件中即可:
[root@SSCVL0436 ~]# /data/mysql/bin/mysqld--defaults-file=/data/mysqldata/my.cnf --initialize --user=mysql
[root@SSCVL0436 ~]# cd /data/mysqldata
[root@SSCVL0436 mysqldata]# ll
total 24
drwxr-xr-x 2 mysql mysql 4096 Apr 1615:23 binlog
drwxr-xr-x 2 mysql mysql 4096 Apr 1615:24 data
drwxr-xr-x 2 mysql mysql 4096 Apr 1615:23 logs
-rw-rw-r-- 1 mysql mysql 1271 Apr 1615:22 my.cnf
drwxr-xr-x 2 mysql mysql 4096 Apr 1610:31 run
drwxr-xr-x 2 mysql mysql 4096 Apr 1615:24 tmp
[root@SSCVL0436 mysqldata]# ls *
my.cnf
binlog:
mysql-bin.000001 mysql-bin.index
data:
auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ib_logfile2 mysql performance_schema sys
logs:
mysql-err.log slow_query.log
run:
tmp:
[root@SSCVL0436 mysqldata]#
如果需要重新建库,那么保留my.cnf,其余目录内的文件全部删除:
cd /data/mysqldata
rm -rf */*
1.1.5.3 启动MySQL server
[root@SSCVL0436 ~]# su - mysql
[mysql@SSCVL0436 ~]$ mysqld_safe--defaults-file=/data/mysqldata/my.cnf
2018-04-16T09:03:30.390366Z mysqld_safeLogging to '/data/mysqldata/logs/mysql-err.log'.
2018-04-16T09:03:30.441253Z mysqld_safeStarting mysqld daemon with databases from /data/mysqldata/data
这是在前台启动。
放后台启动:
[mysql@SSCVL0436 ~]$ mysqld_safe--defaults-file=/data/mysqldata/my.cnf &
1.1.5.4 设置自动启动
复制mysql.server文件到自动启动目录:
[root@SSCVL0436 support-files]# pwd
/data/mysql/support-files
[root@SSCVL0436 support-files]# ll
total 24
-rw-r--r-- 1 root mysql 773 Dec 28 11:46 magic
-rwxr-xr-x 1 root mysql 1061 Apr 16 10:06 mysqld_multi.server
-rwxr-xr-x 1 root mysql 877 Apr 16 10:06 mysql-log-rotate
-rwxr-xr-x 1 root mysql 10550 Apr 1610:06 mysql.server
[root@SSCVL0436 support-files]# cp mysql.server /etc/rc.d/init.d/mysqld
[root@SSCVL0436 support-files]# ll/etc/rc.d/init.d/mysqld
-rwxr-xr-x 1 root root 10550 Apr 17 00:16/etc/rc.d/init.d/mysqld
添加到自动启动:
chkconfig--list|grep mysqld
chkconfig --add mysqld
chkconfig mysqld on
service mysqld status
[root@SSCVL0436 ~]# chkconfig --list|grepmysqld
[root@SSCVL0436 ~]# chkconfig --addmysqld
[root@SSCVL0436 ~]# chkconfig--list|grep mysqld
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@SSCVL0436 ~]#
[root@SSCVL0436 ~]# service mysqld status
MySQL is not running [FAILED]
[root@SSCVL0436 ~]#
[root@SSCVL0436 ~]# service mysqld start
Starting MySQL. [ OK ]
[root@SSCVL0436 ~]# service mysqldstatus
MySQL running (10139) [ OK ]
查看进程:
[root@SSCVL0436 ~]# ps -ef|grep mysql
root 9585 1 000:28 pts/2 00:00:00 /bin/sh/data/mysql/bin/mysqld_safe --datadir=/data/mysqldata/data--pid-file=/data/mysqldata/run/mysql.pid
mysql 10139 9585 000:28 pts/2 00:00:00/data/mysql/bin/mysqld --basedir=/data/mysql --datadir=/data/mysqldata/data--plugin-dir=/data/mysql/lib/plugin --user=mysql--log-error=/data/mysqldata/logs/mysql-err.log --open-files-limit=10240--pid-file=/data/mysqldata/run/mysql.pid--socket=/data/mysqldata/run/mysql.sock --port=3306
1.1.5.5 问题处理
1.启动MySQL server时,错误日志中出现关于TIMESTAMP的告警
2018-04-16T09:03:30.665679Z 0 [Warning]TIMESTAMP with implicit DEFAULT value is deprecated. Please use--explicit_defaults_for_timestamp server option (see documentation for moredetails).
处理方法:
在MySQL参数文件中增加参数:
explicit_defaults_for_timestamp = true
参考:
MySQL 5.7中timestamp数据类型和explicit_defaults_for_timestamp参数:
http://www.ywnds.com/?p=8309
2.MySQL server启动之后,不用密码不能登录
[mysql@SSCVL0436 ~]$ mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user'root'@'localhost' (using password: NO)
这是正常现象。在5.7版本中,安全策略有所调整,root账户不再像5.6一样可以使用空密码登录。当mysql初始启动时会为root账户随机生成一个密码,存储在log-error文件中:
[mysql@SSCVL0436 logs]$ pwd
/data/mysqldata/logs
[mysql@SSCVL0436 logs]$ cat mysql-err.log|grep password
2018-04-16T09:00:17.841880Z 1 [Note] Atemporary password is generated for root@localhost: V)3A5KtNevLj
2018-04-16T09:05:56.521500Z 2 [Note]Access denied for user 'root'@'localhost' (using password: NO)
使用上述密码进行登录:
[mysql@SSCVL0436 logs]$ mysql -uroot-p'V)3A5KtNevLj'
mysql: [Warning] Using a password on thecommand line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-log
Copyright (c) 2000, 2018, Oracle and/orits affiliates. All rights reserved.
Oracle is a registered trademark ofOracle Corporation and/or its
affiliates. Other names may betrademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c'to clear the current input statement.
mysql> showdatabases;
ERROR 1820 (HY000): You must reset yourpassword using ALTER USER statement before executing this statement.
成功登录,但执行命令报错。
根据提示修改密码:
mysql> alter user'root'@'localhost' identified by 'elcar0123';
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
密码修改后,命令即可正常执行。
1.2 拷贝安装
源码包在某个服务器上经过编译安装后,在后续同类型机器上,可直接进行拷贝安装。
1.2.1 安装编译工具以及依赖包
参考前述章节。
注意事项:
我并不打算将参数文件放在/etc目录下,所以,该目录下的my.cnf文件需要删除或重命名,以免干扰后续MySQLserver的启动。
1.2.2 创建mysql账户
参考前述章节。
注意事项:
mysql账户的uid和gid要和源服务器上的相同。
补充:uid, gid与源服务器不相同好像也没问题。
1.2.3 源端打包mysql安装目录
要以root账户来做。
[root@SSCVL0436 data]# id
uid=0(root) gid=0(root) groups=0(root)
[root@SSCVL0436 data]# pwd
/data
[root@SSCVL0436 data]# ll
total 28
drwx------ 2 root root 16384 Apr 10 15:16lost+found
drwxr-xr-x 10 root mysql 4096 Apr 16 10:27 mysql
drwxr-xr-x 7 mysql mysql 4096 Apr 16 23:54 mysqldata
drwxr-xr-x 3 root root 4096 Apr 16 09:54 soft
[root@SSCVL0436 data]# tar -cf mysql.tar mysql
[root@SSCVL0436 data]# ll
total 2021764
drwx------ 2 root root 16384 Apr 10 15:16lost+found
drwxr-xr-x 10 root mysql 4096 Apr 16 10:27 mysql
drwxr-xr-x 7 mysql mysql 4096 Apr 16 23:54 mysqldata
-rw-r--r-- 1 root root 2070251520 Apr 17 00:57 mysql.tar
drwxr-xr-x 3 root root 4096 Apr 16 09:54 soft
将mysql.tar传至目标服务器:
[root@SSCVL0436 data]# scp mysql.tar10.64.20.86:/data
1.2.4 目标服务器上解包
要以root账户来解包。
[root@SSCVL0437 data]# id
uid=0(root) gid=0(root) groups=0(root)
[root@SSCVL0437 data]# pwd
/data
[root@SSCVL0437 data]# ll
total 2021752
drwx------ 2 root root 16384 Apr 10 15:41 lost+found
-rw-r--r-- 1 root root 2070251520 Apr 1700:59 mysql.tar
[root@SSCVL0437 data]# tar -xf mysql.tar
[root@SSCVL0437 data]# ll
total 2021756
drwx------ 2 root root 16384 Apr 10 15:41 lost+found
drwxr-xr-x 10 root mysql 4096 Apr 16 10:27 mysql
-rw-r--r-- 1 root root 2070251520 Apr 1700:59 mysql.tar
1.2.5 建库
建立数据目录并修改权限:
mkdir -p/data/mysqldata/{run,data,tmp,binlog,logs}
chown -R mysql:mysql /data/mysqldata
从源服务器拷贝参数文件到目标服务器。
修改参数文件:
server-id = 86
修改此处即可。
注意:从这里开始的操作,与前述安装完全相同。
初始化建库:
[root@SSCVL0437 ~]# /data/mysql/bin/mysqld--defaults-file=/data/mysqldata/my.cnf --initialize --user=mysql
启动MySQL server(前台):
[root@SSCVL0437 mysqldata]# su - mysql
[mysql@SSCVL0437 ~]$ mysqld_safe--defaults-file=/data/mysqldata/my.cnf
2018-04-16T17:26:47.147454Z mysqld_safeLogging to '/data/mysqldata/logs/mysql-err.log'.
2018-04-16T17:26:47.213064Z mysqld_safeStarting mysqld daemon with databases from /data/mysqldata/data
设置自动启动:
略。
第 2 章 MySQL复制
2.1 全新环境构建异步复制
新构建的MySQL环境,里面没有应用账户,也没有应用数据库。
复制环境:
MySQL版本:MySQL5.7.21
Master Server: 10.64.22.95
Slave Server : 10.64.22.96
2.1.1 Master服务器配置
1.修改参数文件,启用binlog
相关参数:
[mysqld]
server-id = 95
log-bin = /data/mysqldata/binlog/mysql-bin
修改参数后,重启MySQL server。
若已配置,跳过该步骤。
2.创建复制账户
grant replication slave on *.* to'repl'@'10.64.22.96' identified by 'my_repl';
或者:
grant replication slave on *.* to'repl'@'10.64.22.%' identified by 'my_repl';
3.记录主库日志信息
[mysql@SSCVL0419 ~]$ mysql -uroot -p
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000009 | 447 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.1.2 Slave服务器配置
1.修改参数文件,启用binlog
相关参数:
[mysqld]
server-id = 96 ###与其值与master server不同
log-bin = /data/mysqldata/binlog/mysql-bin ###可选,反向、级联复制需要
修改参数后,重启MySQL server。
若已配置,跳过该步骤。
2.创建复制账户
grant replication slave on *.* to'repl'@'10.64.22.95' identified by 'my_repl';
或者:
grant replication slave on *.* to'repl'@'10.64.22.%' identified by 'my_repl';
这一步对于slave来说不是必须的,用来方便主从切换。
3.Slave上设置日志读取信息
change master to
master_host='10.64.22.95',
master_port=3306,
master_user='repl',
master_password='my_repl',
master_log_file='mysql-bin.000009',
master_log_pos=447;
4.Slave上启动复制线程
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
2.1.3 查看复制状态
登录slave服务器。
mysql> show slave status\G
*************************** 1. row***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 10.64.22.95
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 447
Relay_Log_File:SSCVL0420-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File:mysql-bin.000009
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: 447
Relay_Log_Space: 531
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: 95
Master_UUID:a2cad19a-41e2-11e8-8030-005056a83905
Master_Info_File:/data/mysqldata/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for moreupdates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
字段说明:
字段 | 说明 |
Slave_IO_Running | IO线程状态 |
Master_Log_File | IO线程正在读取master库上哪个binlog |
Read_Master_Log_Pos | IO线程正在读取master库上该binlog的哪个位置点 |
Slave_SQL_Running | SQL线程状态 |
Relay_Master_Log_File | SQL线程正在应用master库的哪个binlog |
Exec_Master_Log_Pos | SQL线程正在应用master库该binlog的哪个位置点 |
Seconds_Behind_Master | Slave落后Master多少秒 |
2.1.4 注意事项
1.主从数据库版本要一样。
2.主从server-uuid要不一样
[mysql@SSCVL0437 data]$ pwd
/data/mysqldata/data
[mysql@SSCVL0437 data]$ cat auto.cnf
[auto]
server-uuid=97db2c88-4199-11e8-8afb-005056a836e9
3.如果不是全新环境,MySQL环境中已有应用账户、应用数据,则还需要数据导出、导入,请参考其他文档。
2.1.5 复制测试
1.Master上操作:
create database lyqiu;
use lyqiu;
create table test(
id int(11) not null auto_increment,
name varchar(100) not null,
dt datetime,
primary key (id)
) engine=innodb auto_increment=2 defaultcharset=utf8;
insert into test(name,dt) values('abc',now()); ###默认自动提交
mysql> select * from test;
+----+------+---------------------+
| id | name | dt |
+----+------+---------------------+
| 2 | abc | 2018-04-18 00:10:31 |
+----+------+---------------------+
1 row in set (0.00 sec)
2.Slave上检查:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lyqiu |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use lyqiu;
Reading table information for completionof table and column names
You can turn off this feature to get aquicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_lyqiu |
+-----------------+
| test |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from test;
+----+------+---------------------+
| id | name | dt |
+----+------+---------------------+
| 2 | abc | 2018-04-18 00:10:31 |
+----+------+---------------------+
1 row in set (0.00 sec)
2.2 常用操作
2.2.1 主库
查看主库binlog状态:
mysql> show master status;
查看master有多少个slave:
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id |Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 86 | | 3306 | 85 | 97db2c88-4199-11e8-8afb-005056a836e9|
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
查看主库binlog:
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 421 |
| mysql-bin.000003 | 177 |
| mysql-bin.000004 | 177 |
| mysql-bin.000005 | 177 |
| mysql-bin.000006 | 177 |
| mysql-bin.000007 | 177 |
| mysql-bin.000008 | 1835 |
+------------------+-----------+
8 rows in set (0.00 sec)
查看binlog中的事件:
mysql> show binlog events in'mysql-bin.000008';
查看进程:
mysql> show processlist\G
*************************** 1. row ***************************
Id: 6
User: repl
Host: 10.64.20.86:48322
db: NULL
Command: Binlog Dump
Time: 33579
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
*************************** 2. row***************************
Id: 7
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
2 rows in set (0.00 sec)
2.2.2 从库
查看从库复制状态:
mysql> show slave status\G
注意可能会过滤mysql.*之类的操作。
启停复制:
mysql> start slave;
mysql> stop slave;
查看进程:
mysql> show processlist\G
*************************** 1. row***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 33799
State: Waiting for master to send event
Info: NULL
*************************** 2. row***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 33480
State: Slave has read all relay log; waiting for more updates
Info: NULL
*************************** 3. row***************************
Id: 5
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
3 rows in set (0.00 sec)