二进制安装
#安装准备
[root@Centos7 ~]# useradd -r -s /sbin/nologin -d /data/mysql mysql
[root@Centos7 ~]# tar -xf mariadb-10.2.25-linux-x86_64.tar.gz -C /usr/local
[root@Centos7 local]# ln -s mariadb-10.2.25-linux-x86_64/ mysql
[root@Centos7 local]# chown -R mysql.mysql mysql/
#安装二进制数据库
[root@Centos7 local]# cd mysql
[root@Centos7 mysql]# scripts/mysql_install_db --datadir=/data/mysql --user=mysql
#准备相关配置文件
[root@Centos7 mysql]# cp support-files/my-huge.cnf /etc/mysql/my.cnf
[root@Centos7 mysql]# mv /etc/my.cnf /etc/my.cnf.bak
[root@Centos7 mysql]# vim /etc/mysql/my.cnf
[mysqld]
datadir=/data/mysql
#准备服务文件
[root@Centos7 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@Centos7 mysql]# chkconfig --add mysqld
[root@Centos7 mysql]# chkconfig mysqld on
[root@Centos7 mysql]# service mysqld start
Starting mysqld (via systemctl): [ OK ]
[root@Centos7 mysql]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@Centos7 mysql]# . /etc/profile.d/mysql.sh
#测试
[root@Centos7 mysql]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.25-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]>
#安全加固
[root@Centos7 mysql]# mysql_secure_installation
源码编译安装
#编译准备
[root@Centos7 ~]$ echo -e 'n\n\n\nt\n\n8e\nw\n' | fdisk /dev/sda
[root@Centos7 ~]$ partprobe
[root@Centos7 ~]$ pvcreate /dev/sda6
[root@Centos7 ~]$ vgcreate db /dev/sda6 -s 16
Volume group "db" successfully created
[root@Centos7 ~]$ lvcreate -n mysql -l +100%free db
Logical volume "mysql" created.
[root@Centos7 ~]$ mkfs.xfs /dev/db/mysql
[root@Centos7 ~]$ vim /etc/fstab
/dev/db/mysql /data/mysql xfs defaults 0 0
[root@Centos7 ~]$ mkdir /data/mysql
[root@Centos7 ~]$ mount -a
[root@Centos7 ~]$ useradd -r -s /sbin/noglogin -d /data/mysql mysql
[root@Centos7 ~]$ chown mysql.mysql /data/mysql
#安装依赖包
[root@Centos7 ~]$ yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel libdb-cxx-devel -y
#编译
[root@Centos7 ~]$ tar xf mariadb-10.2.25.tar.gz
[root@Centos7 ~]$ cd mariadb-10.2.25/
[root@Centos7 mariadb-10.2.25]$ cmake . -DCMAKE_INSTALL_PREFIX=/apps/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/ \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8_general_ci
[root@Centos7 mariadb-10.2.25]$ make && make install
[root@Centos7 mariadb-10.2.25]$ chown mysql.mysql /apps/mysql
#编译后一些准备工作
#准备环境变量
echo 'PATH=/apps/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
#生成数据库文件
cd /apps/mysql/
scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql
#准备配置文件
cp /apps/mysql/support-files/my-huge.cnf /etc/my.cnf
#准备启动脚本
cp /apps/mysql/support-files/mysql.server /etc/init.d/mysqld
#启动服务
chkconfig --add mysqld ;service mysqld start
二进制安装的多实例
#删除原二进制安装的相关文件，保留环境变量
[root@Centos7 mysql]# service mysqld stop
[root@Centos7 mysql]# chkconfig --del mysqld
[root@Centos7 mysql]# rm -rf /data/mysql/*
#建立多个实例目录
[root@Centos7 mysql]# mkdir -pv /data/mysql/{3306,3307,3308}/{bin,data,etc,log,pid,socket}
[root@Centos7 mysql]# chown -R mysql.mysql /data/mysql/
[root@Centos7 mysql]# tree /data/mysql/
/data/mysql/
├── 3306
│ ├── bin
│ ├── data
│ ├── etc
│ ├── log
│ ├── pid
│ └── socket
├── 3307
│ ├── bin
│ ├── data
│ ├── etc
│ ├── log
│ ├── pid
│ └── socket
└── 3308
├── bin
├── data
├── etc
├── log
├── pid
└── socket
21 directories, 0 files
#二进制安装
[root@Centos7 mysql]# pwd
/usr/local/mysql
[root@Centos7 mysql]# ./scripts/mysql_install_db --datadir=/data/mysql/3306/data --user=mysql
[root@Centos7 mysql]# ./scripts/mysql_install_db --datadir=/data/mysql/3307/data --user=mysql
[root@Centos7 mysql]# ./scripts/mysql_install_db --datadir=/data/mysql/3308/data --user=mysql
#mysql配置文件
[root@Centos7 mysql]# cd /data/mysql/
[root@Centos7 mysql]# vim 3306/etc/my.cnf
[mysqld]
port=3306
datadir=/data/mysql/3306/data
socket=/data/mysql/3306/socket/mysql.sock
[mysqld_safe]
log-error=/data/mysql/3306/log/mysql.log
pid-file=/data/mysql/3306/pid/mysql.pid
[root@Centos7 mysql]# chown mysql.mysql -R 3306/
[root@Centos7 mysql]# cp -p 3306/etc/my.cnf 3307/etc/
[root@Centos7 mysql]# cp -p 3306/etc/my.cnf 3308/etc/
[root@Centos7 mysql]# sed -i 's/3306/3307/' 3307/etc/my.cnf
[root@Centos7 mysql]# sed -i 's/3306/3308/' 3308/etc/my.cnf
#配置启动服务文件
[root@Centos7 mysql]# vim 3306/bin/mysqld
#!/bin/bash
port=3306
mysql_user="root"
mysql_pwd="centos"
cmd_path="/usr/local/mysql/bin"
mysql_basedir="/data/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &
else
printf "MySQL is running...\n"
exit
fi
}
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
fi
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac
[root@Centos7 mysql]# chown mysql.mysql 3306/bin/mysqld
[root@Centos7 mysql]# chmod +x 3306/bin/mysqld
[root@Centos7 mysql]# cp -p 3306/bin/mysqld 3307/bin/
[root@Centos7 mysql]# cp -p 3306/bin/mysqld 3308/bin/
[root@Centos7 mysql]# sed -i 's/3306/3307/' 3307/bin/mysqld
[root@Centos7 mysql]# sed -i 's/3306/3308/' 3308/bin/mysqld
#启动服务测试
[root@Centos7 mysql]# mysqladmin -h127.0.0.1 -P3308 password centos
[root@Centos7 mysql]# mysqladmin -h127.0.0.1 -P3307 password centos
[root@Centos7 mysql]# mysqladmin -h127.0.0.1 password centos
[root@Centos7 mysql]# /data/mysql/3306/bin/mysqld start
Starting MySQL...
[root@Centos7 mysql]# /data/mysql/3307/bin/mysqld start
Starting MySQL...
[root@Centos7 mysql]# /data/mysql/3308/bin/mysqld start
Starting MySQL...
[root@Centos7 mysql]# ss -ntl | grep 330
LISTEN 0 80 :::3306 :::*
LISTEN 0 80 :::3307 :::*
LISTEN 0 80 :::3308 :::*
#设为开机启动
[root@Centos7 mysql]# echo "/mysql/3306/bin/mysqld start" >> /etc/profile.d/mysql.sh
[root@Centos7 mysql]# echo "/mysql/3307/bin/mysqld start" >> /etc/profile.d/mysql.sh
[root@Centos7 mysql]# echo "/mysql/3308/bin/mysqld start" >> /etc/profile.d/mysql.sh
#客户端连接测试
[root@Centos7 mysql]# mysql -S /data/mysql/3306/socket/mysql.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.25-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]>
[root@Centos7 mysql]# mysql -h127.0.0.1 -P3307 -uroot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.25-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]>
[root@Centos7 mysql]# mysql -h127.0.0.1 -P3308 -uroot -e "show databases"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
DDL(create,drop,alter) DML（insert,delete,update）
MariaDB [(none)]> create database test2; --创建数据库
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database test3;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use test2
MariaDB [test2]> create table student(id int unsigned auto_increment primary key,name varchar(20) not null,gender ENUM('m','f') default 'm', mobile char(11)); --创建表
Query OK, 0 rows affected (0.00 sec)
MariaDB [test2]> desc student; --查看表结构
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| gender | enum('m','f') | YES | | m | |
| mobile | char(11) | YES | | NULL | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
MariaDB [test2]> show TABLE status \G; --查看表字符集
MariaDB [test2]> create table student2 like test2.student; --创建一样结构的表，继承字符集
ariaDB [test2]> create table student3 select * from test2.student; --创建一样结构和内容的表，但字符集不继承
MariaDB [test2]> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| gender | enum('m','f') | YES | | m | |
| mobile | char(11) | YES | | NULL | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
MariaDB [test2]> desc student2;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| gender | enum('m','f') | YES | | m | |
| mobile | char(11) | YES | | NULL | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
MariaDB [test2]> desc student3;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | | 0 | |
| name | varchar(20) | NO | | NULL | |
| gender | enum('m','f') | YES | | m | |
| mobile | char(11) | YES | | NULL | |
+--------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
MariaDB [test2]> insert student(name,mobile)values('tom','13800138000');
MariaDB [test2]> insert student(name,mobile)values('xiaoming','10086'),('xiaohong','10010');
MariaDB [test2]> insert student(name,mobile,gender)values('alice','10000','f');
MariaDB [test2]> alter table student change name name varchar(20), character set utf8mb4; --修改字段字符集
MariaDB [test2]> alter table student change gender gender ENUM('m','f') default 'm', character set utf8mb4;
MariaDB [test2]> alter table student change mobile mobile char(11), character set utf8mb4;
MariaDB [test2]> show full columns from student;
+--------+------------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------+------------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| name | varchar(20) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | |
| gender | enum('m','f') | utf8mb4_general_ci | YES | | m | | select,insert,update,references | |
| mobile | char(11) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | |
+--------+------------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
4 rows in set (0.00 sec)
MariaDB [test2]> show tables; --显示所有表
+-----------------+
| Tables_in_test2 |
+-----------------+
| student |
| student2 |
| student3 |
+-----------------+
3 rows in set (0.01 sec)
MariaDB [test2]> drop table student3; --删除表
MariaDB [test2]> show databases; --显示所有数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test2 |
| test3 |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [test2]> drop database test3; --删除数据库
Query OK, 0 rows affected (0.00 sec)
#修改默认字符集
[root@Centos7 mysql]# vim /etc/my.cnf #服务端
[mysqld]
character_set_server=utf8mb4
[root@Centos7 mysql]# vim /etc/my.cnf.d/mysql-clients.cnf #客户端
[mysql]
default-character-set=utf8mb4
[root@Centos7 mysql]# systemctl restart mariadb
[root@Centos7 mysql]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 2
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.60-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 9 sec
Threads: 1 Questions: 5 Slow queries: 0 Opens: 0 Flush tables: 2 Open tables: 26 Queries per second avg: 0.555
--------------
[root@Centos7 ~]# mysql < hellodb_innodb.sql --导入数据库
MariaDB [(none)]> use hellodb
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
MariaDB [hellodb]> insert students set name="mage",age=30,gender="M";
MariaDB [hellodb]> insert students(name,age) select name,age from teachers;
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | mage | 30 | M | NULL | NULL |
| 27 | Song Jiang | 45 | F | NULL | NULL |
| 28 | Zhang Sanfeng | 94 | F | NULL | NULL |
| 29 | Miejue Shitai | 77 | F | NULL | NULL |
| 30 | Lin Chaoying | 93 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
30 rows in set (0.00 sec)
MariaDB [hellodb]> update students set ClassID=1 where StuID=25;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> select * from students where stuid=25;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | 1 | NULL |
+-------+-------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
SQL DQL(select)
导入hellodb.sql生成数据库
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
MariaDB [hellodb]> select name,age from students where age>25 and gender="M";
+--------------+-----+
| name | age |
+--------------+-----+
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Yu Yutong | 26 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
| Xu Xian | 27 |
| Sun Dasheng | 100 |
+--------------+-----+
7 rows in set (0.00 sec)
(2) 以ClassID为分组依据，显示每组的平均年龄
MariaDB [hellodb]> select classid,avg(age) from students where classid is not null group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+----------+
7 rows in set (0.00 sec)
(3) 显示第2题中平均年龄大于30的分组及平均年龄
MariaDB [hellodb]> select classid,avg(age) from students where classid is not null group by classid having avg(age)>30;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+----------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select classid,avg(age) as 平均年龄 from students where classid is not null group by classid having 平均年龄 > 30;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+--------------+
3 rows in set (0.00 sec)
(4) 显示以L开头的名字的同学的信息
MariaDB [hellodb]> select * from students where name like 'l%' ;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
(5) 显示TeacherID非空的同学的相关信息
MariaDB [hellodb]> select * from students where teacherid is not null ;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
+-------+-------------+-----+--------+---------+-----------+
5 rows in set (0.00 sec)
(6) 以年龄排序后，显示年龄最大的前10位同学的信息
MariaDB [hellodb]> select * from students order by age desc limit 10 ;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息
MariaDB [hellodb]> select * from students where age between 20 and 25 ;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students where age>=20 and age<=25 ;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
1、以ClassID分组，显示每班的同学的人数
MariaDB [hellodb]> select classid as 班级,count(classid) as 人数 from students where classid is not null group by classid;
+--------+--------+
| 班级 | 人数 |
+--------+--------+
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+--------+--------+
7 rows in set (0.00 sec)
MariaDB [hellodb]>
2、以Gender分组，显示其年龄之和
MariaDB [hellodb]> select gender as 性别,sum(age) as 年龄 from students group by gender;
+--------+--------+
| 性别 | 年龄 |
+--------+--------+
| F | 190 |
| M | 495 |
+--------+--------+
2 rows in set (0.00 sec)
3、以ClassID分组，显示其平均年龄大于25的班级
MariaDB [hellodb]> select classid as 班级,avg(age) as 年龄 from students where classid is not null group by classid having 年龄>25;
+--------+---------+
| 班级 | 年龄 |
+--------+---------+
| 2 | 36.0000 |
| 5 | 46.0000 |
+--------+---------+
2 rows in set (0.00 sec)
4、以Gender分组，显示各组中年龄大于25的学员的年龄之和
MariaDB [hellodb]> select gender as 性别,sum(age) as 年龄 from students where age > 25 group by gender;
+--------+--------+
| 性别 | 年龄 |
+--------+--------+
| M | 317 |
+--------+--------+
1 row in set (0.00 sec)
5、显示前5位同学的姓名、课程及成绩
MariaDB [hellodb]> select st.name as 姓名,co.course as 课程,sc.score as 分数 from students as st
inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid limit 5;
+-------------+----------------+--------+
| 姓名 | 课程 | 分数 |
+-------------+----------------+--------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
+-------------+----------------+--------+
5 rows in set (0.00 sec)
6、显示其成绩高于80的同学的名称及课程
MariaDB [hellodb]> MariaDB [hellodb]> select st.name as 姓名,co.course as 课程 from students as st
inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid where score>80;
+-------------+----------------+
| 姓名 | 课程 |
+-------------+----------------+
| Shi Zhongyu | Weituo Zhang |
| Shi Potian | Daiyu Zanghua |
| Xie Yanke | Kuihua Baodian |
| Ding Dian | Kuihua Baodian |
| Shi Qing | Hamo Gong |
| Xi Ren | Hamo Gong |
| Xi Ren | Dagou Bangfa |
| Lin Daiyu | Jinshe Jianfa |
+-------------+----------------+
8 rows in set (0.00 sec)
7、取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
MariaDB [hellodb]> select st.name as 姓名,avg(sc.score) as 成绩 from scores as sc
inner join students as st on st.stuid=sc.stuid group by 姓名 order by 成绩 desc limit 3;
+-------------+---------+
| 姓名 | 成绩 |
+-------------+---------+
| Shi Qing | 96.0000 |
| Shi Zhongyu | 85.0000 |
| Xi Ren | 84.5000 |
+-------------+---------+
3 rows in set (0.00 sec)
8、显示每门课程课程名称及学习了这门课的同学的个数
MariaDB [hellodb]> select co.course as 课程,count(st.name) as 人数 from students as st
inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid group by 课程;
+----------------+--------+
| 课程 | 人数 |
+----------------+--------+
| Dagou Bangfa | 2 |
| Daiyu Zanghua | 2 |
| Hamo Gong | 3 |
| Jinshe Jianfa | 1 |
| Kuihua Baodian | 4 |
| Taiji Quan | 1 |
| Weituo Zhang | 2 |
+----------------+--------+
7 rows in set (0.00 sec)
9、显示其年龄大于平均年龄的同学的名字
MariaDB [hellodb]> select name from students where age > (select avg(age) from students ) ;
+--------------+
| name |
+--------------+
| Xie Yanke |
| Ding Dian |
| Shi Qing |
| Tian Boguang |
| Sun Dasheng |
+--------------+
5 rows in set (0.00 sec)
10、显示其学习的课程为第1、 2，4或第7门课的同学的名字
MariaDB [hellodb]> select st.name as 姓名,sc.score as 成绩,co.course as 课程 from scores as sc
inner join students as st on st.stuid=sc.stuid inner join courses as co on co.courseid=sc.courseid
where co.courseid in (1,2,4) or co.courseid = 7;
+-------------+--------+----------------+
| 姓名 | 成绩 | 课程 |
+-------------+--------+----------------+
| Yu Yutong | 39 | Hamo Gong |
| Shi Qing | 96 | Hamo Gong |
| Xi Ren | 86 | Hamo Gong |
| Shi Zhongyu | 77 | Kuihua Baodian |
| Shi Potian | 47 | Kuihua Baodian |
| Xie Yanke | 88 | Kuihua Baodian |
| Ding Dian | 89 | Kuihua Baodian |
| Lin Daiyu | 57 | Taiji Quan |
| Yu Yutong | 63 | Dagou Bangfa |
11、显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
MariaDB [hellodb]> select st.name as '姓名',st.age as '年龄',st.classid as '班级',tmp.平均年龄 from students as st left join (select classid from students group by classid having classid is not null and count(name) > 3) as c on st.classid=c.classid,(select classid,avg(age) as '平均年龄' from students group by classid ) as tmp where st.classid=tmp.classid and st.age > tmp.平均年龄 order by 班级;
+---------------+--------+--------+--------------+
| 姓名 | 年龄 | 班级 | 平均年龄 |
+---------------+--------+--------+--------------+
| Shi Potian | 22 | 1 | 20.5000 |
| Xu Zhu | 21 | 1 | 20.5000 |
| Xie Yanke | 53 | 2 | 36.0000 |
| Yu Yutong | 26 | 3 | 20.2500 |
| Lin Chong | 25 | 4 | 24.7500 |
| Ding Dian | 32 | 4 | 24.7500 |
| Yuan Chengzhi | 23 | 6 | 20.7500 |
| Huang Yueying | 22 | 6 | 20.7500 |
| Hua Rong | 23 | 7 | 19.6667 |
+---------------+--------+--------+--------------+
9 rows in set (0.00 sec)
MariaDB [hellodb]> select student.name,student.age,student.classid,second.avg_age from (select students.name as name ,students.age as age,students.classid as classid from students left join (select count(name) as num,classid as classid from students group by classid having num>=3) as first on first.classid=students.classid) as student, (select avg(age) as avg_age,classid as classid from students group by classid) as second where student.age>second.avg_age and student.classid=second.classid;
+---------------+-----+---------+---------+
| name | age | classid | avg_age |
+---------------+-----+---------+---------+
| Shi Potian | 22 | 1 | 20.5000 |
| Xie Yanke | 53 | 2 | 36.0000 |
| Ding Dian | 32 | 4 | 24.7500 |
| Yu Yutong | 26 | 3 | 20.2500 |
| Yuan Chengzhi | 23 | 6 | 20.7500 |
| Xu Zhu | 21 | 1 | 20.5000 |
| Lin Chong | 25 | 4 | 24.7500 |
| Hua Rong | 23 | 7 | 19.6667 |
| Huang Yueying | 22 | 6 | 20.7500 |
+---------------+-----+---------+---------+
9 rows in set (0.00 sec)
12、统计各班级中年龄大于全校同学平均年龄的同学
MariaDB [hellodb]> select name as 姓名,age as 年龄,classid as 班级 from students where age > (select avg(age) from students);
+--------------+--------+--------+
| 姓名 | 年龄 | 班级 |
+--------------+--------+--------+
| Xie Yanke | 53 | 2 |
| Ding Dian | 32 | 4 |
| Shi Qing | 46 | 5 |
| Tian Boguang | 33 | 2 |
| Sun Dasheng | 100 | NULL |
+--------------+--------+--------+
5 rows in set (0.00 sec)
mysql mysqladmin
[root@Centos7 ~]# mysqladmin -uroot password 'centos' #设置密码
[root@Centos7 ~]# mysqladmin -uroot -pcentos password 'magedu' #修改密码
explain(索引)
MariaDB [hellodb]> show index from students\G -- 查索引
*************************** 1. row ***************************
Table: students
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: StuID
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
-- 建立索引
MariaDB [hellodb]> create index idx_age on students(age);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> explain select * from students where age=20;
+------+-------------+----------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | students | ref | idx_age | idx_age | 1 | const | 2 | |
+------+-------------+----------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> create index idx_name on students(name(10));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> explain select * from students where name like 's%'; -- s开头的可以利用索引，s开头只有一个
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | students | range | idx_name | idx_name | 32 | NULL | 4 | Using where |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> explain select * from students where name like 'x%'; -- x开头的没有利用索引，因为x开头的有多个
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | idx_name | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show index from students\G
*************************** 1. row ***************************
Table: students
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: StuID
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: students
Non_unique: 1
Key_name: idx_age
Seq_in_index: 1
Column_name: Age
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: students
Non_unique: 1
Key_name: idx_name
Seq_in_index: 1
Column_name: Name
Collation: A
Cardinality: 25
Sub_part: 10
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec)
--删除索引
MariaDB [hellodb]> drop index idx_name on students;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> drop index idx_age on students;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 复合索引
MariaDB [hellodb]> create index idx_name_age on students(name,age);
explain select * from students where name like 's%'; -- 利用索引
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
| 1 | SIMPLE | students | range | idx_name_age | idx_name_age | 152 | NULL | 4 | Using index condition |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> explain select * from students where age=20; -- 跳过name，索引不利用
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> explain select * from students where name like 's%' and age=20; --可利用索引
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
| 1 | SIMPLE | students | range | idx_name_age | idx_name_age | 153 | NULL | 4 | Using index condition |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> explain select * from students where stuid > 10; --主键可利用索引
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | students | range | PRIMARY | PRIMARY | 4 | NULL | 15 | Using where |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> explain select * from students where stuid + 10 > 20; --主键运算不能利用索引
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
-- 开启索引记录
[root@Centos7 ~]# vim /etc/my.cnf
[mysqld]
userstat
[root@Centos7 ~]# systemctl restart mariadb
MariaDB [hellodb]> show variables like 'userstat';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| userstat | ON |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from students where stuid > 10;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
15 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students where stuid = 10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
+-------+--------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show index_statistics;
+--------------+------------+------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+------------+------------+-----------+
| hellodb | students | PRIMARY | 16 |
+--------------+------------+------------+-----------+
1 row in set (0.00 sec)
-- 导入数据库做实验
[root@Centos7 ~]# cat testlog.sql
create table testlog (id int auto_increment primary key,name char(10),age int default 20);
delimiter $$
create procedure sp_testlog()
begin
declare i int;
set i = 1;
while i <= 1000000
do insert into testlog(name,age) values (concat('wang',i),i);
set i = i +1;
end while;
end$$
delimiter ;
[root@Centos7 ~]# mysql hellodb < testlog.sql
MariaDB [hellodb]> call sp_testlog;
Query OK, 1 row affected, 1 warning (4 min 53.51 sec) -- 100W条导入时间4分53秒
MariaDB [hellodb]> select count(name) from testlog;
+-------------+
| count(name) |
+-------------+
| 1000000 |
+-------------+
1 row in set (0.22 sec)
-- 创建索引前查询的时间
MariaDB [hellodb]> select * from testlog where name='wang99999';
+-------+-----------+-------+
| id | name | age |
+-------+-----------+-------+
| 99999 | wang99999 | 99999 |
+-------+-----------+-------+
1 row in set (0.29 sec)
MariaDB [hellodb]> explain select * from testlog where name='wang99999';
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | testlog | ALL | NULL | NULL | NULL | NULL | 1000495 | Using where |
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from testlog where name='wang499999';
+--------+------------+--------+
| id | name | age |
+--------+------------+--------+
| 499999 | wang499999 | 499999 |
+--------+------------+--------+
1 row in set (0.37 sec)
MariaDB [hellodb]> explain select * from testlog where name='wang499999';
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | testlog | ALL | NULL | NULL | NULL | NULL | 1000495 | Using where |
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
-- 创建唯一键索引后查询时间
MariaDB [hellodb]> create index idx_name on testlog(name);
Query OK, 0 rows affected (4.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> select * from testlog where name='wang99999';
+-------+-----------+-------+
| id | name | age |
+-------+-----------+-------+
| 99999 | wang99999 | 99999 |
+-------+-----------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> explain select * from testlog where name='wang99999';
+------+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | testlog | ref | idx_name | idx_name | 31 | const | 1 | Using index condition |
+------+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from testlog where name='wang499999';
+--------+------------+--------+
| id | name | age |
+--------+------------+--------+
| 499999 | wang499999 | 499999 |
+--------+------------+--------+
1 row in set (0.00 sec)
MariaDB [hellodb]> explain select * from testlog where name='wang499999';
+------+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | testlog | ref | idx_name | idx_name | 31 | const | 1 | Using index condition |
+------+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show index from testlog\G;
*************************** 1. row ***************************
Table: testlog
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 1000544
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: testlog
Non_unique: 1
Key_name: idx_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.01 sec)
MariaDB [hellodb]> drop index idx_name on testlog;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
事务日志
#事务日志作用：先修改内存数据，然后慢慢同步到硬盘
#事务日志分离
[root@Centos7 ~]# vim /etc/my.cnf
[mysqld]
innodb_log_group_home_dir=/data/logs
[root@Centos7 ~]# mkdir /data/logs
[root@Centos7 ~]# chown mysql.mysql /data/logs/
[root@Centos7 ~]# systemctl restart mariadb
MariaDB [(none)]> select @@tx_isolation #查看事务日志隔离级别
-> ;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
#永久修改事务日志隔离级别
#四个级别
#读未提交（read-uncommitted） #可读到未提交的数据，产生脏读
#不可重复读（read-committed） #可读到已提交的多个数据，导致每次读取数据都不一样
#可重复读（repeatable-read） #默认,多次读取的数据都一样
#串行化（serializable） #未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE
#-----------------分割线------------------#
#使用事务执行储存过程
MariaDB [hellodb]> show variables like 'autocommit'; #自动提交功能(默认on)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> set autocommit=off; #临时关闭自动提交功能
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
#事务使用
#刚刚导入用时4分53秒，现在看看使用事务导入时间
MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> select count(name) from testlog;
+-------------+
| count(name) |
+-------------+
| 1000000 | #导入前行数
+-------------+
1 row in set (0.25 sec)
MariaDB [hellodb]> call sp_testlog;
Query OK, 1 row affected, 1 warning (13.45 sec) #仅使用了13秒
MariaDB [hellodb]> select count(name) from testlog;
+-------------+
| count(name) |
+-------------+
| 2000000 | #导入后行数
+-------------+
1 row in set (0.49 sec)
MariaDB [hellodb]> commit; #由于关闭了自动提交，所以需要手动提交才生效
Query OK, 0 rows affected (0.00 sec)
慢查询日志
-- 查看慢查询记录是否开启
MariaDB [hellodb]> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.00 sec)
-- 开启慢查询日志(默认关闭)
[root@Centos7 ~]# vim /etc/my.cnf
[mysqld]
slow_query_log=on
long_query_time=3
log_queries_not_using_indexes=on
[root@Centos7 ~]# systemctl restart mariadb
-- 开启慢查询记录
MariaDB [hellodb]> set profiling=on;
Query OK, 0 rows affected (0.00 sec)
-- 查询
MariaDB [hellodb]> select sleep(1) from teachers;
+----------+
| sleep(1) |
+----------+
| 0 |
| 0 |
| 0 |
| 0 |
+----------+
4 rows in set (4.01 sec)
-- 查看语句记录
MariaDB [hellodb]> show profiles;
+----------+------------+-------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------+
| 1 | 0.00016303 | SELECT DATABASE() |
| 2 | 0.00048756 | show databases |
| 3 | 0.00036029 | show tables |
| 4 | 0.00030669 | select sleep(1) from teathers |
| 5 | 4.00410334 | select sleep(1) from teachers |
+----------+------------+-------------------------------+
5 rows in set (0.00 sec)
-- 查看语句过程执行时间
MariaDB [hellodb]> show profile for query 5;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000073 |
| checking permissions | 0.000009 |
| Opening tables | 0.000018 |
| After opening tables | 0.000018 |
| System lock | 0.000007 |
| Table lock | 0.000003 |
| After table lock | 0.000006 |
| init | 0.000020 |
| optimizing | 0.000009 |
| statistics | 0.000018 |
| preparing | 0.000011 |
| executing | 0.000003 |
| Sending data | 0.000173 |
| User sleep | 1.000521 |
| User sleep | 1.000993 |
| User sleep | 1.001035 |
| User sleep | 1.001015 |
| end | 0.000019 |
| query end | 0.000034 |
| closing tables | 0.000021 |
| freeing items | 0.000008 |
| updating status | 0.000024 |
| logging slow query | 0.000062 |
| cleaning up | 0.000003 |
+----------------------+----------+
24 rows in set (0.00 sec)
-- 日志记录
[root@Centos7 ~]# tail -10 /var/lib/mysql/Centos7-slow.log
/usr/libexec/mysqld, Version: 5.5.60-MariaDB (MariaDB Server). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 200329 17:41:56
# User@Host: root[root] @ localhost []
# Thread_id: 2 Schema: hellodb QC_hit: No
# Query_time: 4.004005 Lock_time: 0.000118 Rows_sent: 4 Rows_examined: 4
use hellodb;
SET timestamp=1585474916;
select sleep(1) from teachers;
*二进制日志
#开启二进制日志，二进制日志对日后恢复起很多作用
[root@Centos7 ~]# vim /etc/my.cnf
[mysqld]
log_bin=/data/logs/mysql-bin
binlog_format=row #默认STATEMENT(记录语句)，ROW每一行修改都记录
[root@Centos7 ~]# systemctl restart mariadb
MariaDB [hellodb]> delete from testlog where id > 800000; #执行删除语句
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| myslq-bin.000001 | 4116396 |
+------------------+-----------+
1 row in set (0.00 sec)
#查看日志
[root@Centos7 ~]# mysqlbinlog --start-position=245 --stop-position=4116396 /data/logs/mysql-bin.000001 -vvv | tail -20
### @2='wang999998' /* STRING(30) meta=65054 nullable=1 is_null=0 */
### @3=999998 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `hellodb`.`testlog`
### WHERE
### @1=999999 /* INT meta=0 nullable=0 is_null=0 */
### @2='wang999999' /* STRING(30) meta=65054 nullable=1 is_null=0 */
### @3=999999 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `hellodb`.`testlog`
### WHERE
### @1=1000000 /* INT meta=0 nullable=0 is_null=0 */
### @2='wang100000' /* STRING(30) meta=65054 nullable=1 is_null=0 */
### @3=1000000 /* INT meta=0 nullable=1 is_null=0 */
# at 4116369
#200329 18:26:19 server id 1 end_log_pos 4116396 Xid = 19
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
*备份还原至最新状态
#先打开二进制日志
[root@Centos7 ~]# vim /etc/my.cnf
[mysqld]
log_bin=/data/logs/mysql-bin
binlog_format=row
[root@Centos7 ~]# systemctl restart mariadb
#完整备份数据库
[root@Centos7 ~]# mysqldump -A --master-data=2 > /data/all.sql
#数据库正常写入数据
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 245 |
+------------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]> use hellodb
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
MariaDB [hellodb]> insert students (name,age)values('a',20);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> insert students (name,age)values('b',30);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 635 |
+------------------+-----------+
1 row in set (0.00 sec)
#模拟故障，恢复数据库
[root@Centos7 ~]# rm -rf /var/lib/mysql/*
[root@Centos7 ~]# vim /etc/my.cnf
[mysqld]
skip_networking
[root@Centos7 ~]# systemctl restart mariadb
MariaDB [(none)]> set sql_log_bin=off; #暂时关闭二进制日志
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 654 |
| mysql-bin.000002 | 26792 |
| mysql-bin.000003 | 921736 |
| mysql-bin.000004 | 264 |
| mysql-bin.000005 | 245 |
+------------------+-----------+
5 rows in set (0.00 sec)
[root@Centos7 ~]# grep -i "master_log_pos" /data/all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;
[root@Centos7 ~]# mysqlbinlog --start-position=245 /data/logs/mysql-bin.000001 > /data/inc.sql
[root@Centos7 ~]# mysqlbinlog /data/logs/mysql-bin.000002 >> /data/inc.sql
[root@Centos7 ~]# mysqlbinlog /data/logs/mysql-bin.000003 >> /data/inc.sql
[root@Centos7 ~]# mysqlbinlog /data/logs/mysql-bin.000004 >> /data/inc.sql
MariaDB [mysql]> source /data/all.sql
MariaDB [mysql]> source /data/inc.sql
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
| test2 |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [mysql]> use hellodb
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | a | 20 | F | NULL | NULL |
| 27 | b | 30 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)
#还原设置
MariaDB [hellodb]> set sql_log_bin=on;
[root@Centos7 ~]# vim /etc/my.cnf
[mysqld]
#skip_networking
[root@Centos7 ~]# systemctl restart mariadb
*恢复误删除的表
#前提：开启了二进制日志，并且记录方式为row
MariaDB [mysql]> set sql_log_bin=on;
#修改表然后删除表
MariaDB [hellodb]> insert teachers (name,age) values ('mage',30);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> insert teachers (name,age) values ('wang',18);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> drop table students;
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 654 |
| mysql-bin.000002 | 26792 |
| mysql-bin.000003 | 921736 |
| mysql-bin.000004 | 264 |
| mysql-bin.000005 | 16895926 |
+------------------+-----------+
5 rows in set (0.00 sec)
#还原刚删除的表
[root@Centos7 ~]# vim /etc/my.cnf
[mysqld]
skip_networking #禁止访问，或者用iptables拦截
[root@Centos7 ~]# systemctl restart mariadb
#由于备份完毕后，二进制日志是从05开始记录，故把它全部导出来
[root@Centos7 ~]# mysqlbinlog --start-position=245 /data/logs/mysql-bin.000005 > /data/inc2.sql
[root@Centos7 ~]# vim /data/inc2.sql
#DROP TABLE `students` /* generated by server */ #最后几行找到drop table，把它注释掉
MariaDB [hellodb]> flush tables with read lock; #全局读锁
MariaDB [hellodb]> set sql_log_bin=off #关闭二进制日志
MariaDB [hellodb]> flush logs;
MariaDB [hellodb]> source /data/inc2.sql
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| testlog |
| toc |
+-------------------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | a | 20 | F | NULL | NULL |
| 27 | b | 30 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)
#还原设置
MariaDB [hellodb]> set sql_log_bin=on;
[root@Centos7 ~]# vim /etc/my.cnf
[mysqld]
#skip_networking
[root@Centos7 ~]# systemctl restart mariadb
xtrabackup 备份还原
#安装，需要EPL源
[root@Centos7 ~]# yum install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm #
#完全备份
[root@Centos7 ~]# mkdir /backup/base
[root@Centos7 ~]# xtrabackup --backup --target-dir=/backup
......
200330 14:19:50 completed OK!
#增量备份
MariaDB [(none)]> use hellodb;
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
MariaDB [hellodb]> insert teachers (name,age) values ('wang',20);
Query OK, 1 row affected (0.00 sec)
[root@Centos7 ~]# xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
MariaDB [(none)]> use hellodb;
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
MariaDB [hellodb]> insert teachers (name,age) values ('zhang',20);
Query OK, 1 row affected (0.00 sec)
[root@Centos7 ~]# xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
#恢复备份(需要停止服务)
[root@Centos7 ~]# systemctl stop mariadb
#预处理
[root@Centos7 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base
[root@Centos7 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
[root@Centos7 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc2
#清空数据库
[root@Centos7 ~]# rm -rf /var/lib/mysql/*
#复制还原
[root@Centos7 ~]# xtrabackup --copy-back --target-dir=/backup/base
[root@Centos7 ~]# chown -R mysql.mysql /var/lib/mysql/
[root@Centos7 ~]# systemctl start mariadb
#还原后查看数据是否存在
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | mage | 30 | NULL |
| 6 | wang | 18 | NULL |
| 7 | wang | 20 | NULL |
| 8 | zhang | 20 | NULL |
+-----+---------------+-----+--------+
8 rows in set (0.00 sec)
*主从复制
#2台新的数据库服务器，ip：17，27
#master_server
[root@master_sql ~]$ yum install mariadb-server mariadb -y
[root@master_sql ~]$ vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=/data/logs/mysql-bin
[root@master_sql ~]$ mkdir /data/logs
[root@master_sql ~]$ chown -R mysql.mysql /data/logs
[root@master_sql ~]$ systemctl start mariadb
[root@master_sql ~]$ mysql -e "grant replication slave on *.* to repluser@'192.168.37.%' identified by 'centos'"
[root@master_sql ~]$ mysql -e "flush privileges"
[root@master_sql ~]$ mysqldump -A -F --single-transaction --master-data=1 > /data/master.sql
[root@master_sql ~]$ scp /data/master.sql 192.168.37.27:/data
#slave_server
[root@slave_sql ~]$ yum install mariadb-server mariadb -y
[root@slave_sql ~]# vim /data/master.sql
CHANGE MASTER TO
MASTER_HOST='192.168.37.17',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=245;
[root@slave_sql ~]# vim /etc/my.cnf
[mysqld]
server-id=2
log-bin=/data/logs/mysql-bin
read-only
[root@slave_sql ~]# mkdir /data/logs
[root@slave_sql ~]# chown mysql.mysql /data/logs/
[root@slave_sql ~]# systemctl start mariadb
MariaDB [test]> source /data/master.sql
MariaDB [test]> start slave;
MariaDB [test]> show slave status\G
*在现有mysql服务器基础上，实现主从复制
#2台数据库服务器，ip：7，27
#old_server
[root@Centos7 ~]# vim /etc/my.cnf
[mysqld]
server-id=7
log_bin=/data/logs/mysql-bin
binlog_format=row
[root@Centos7 ~]# systemctl restart mariadb
[root@Centos7 ~]# mysql -e "grant replication slave on *.* to repluser@'192.168.37.%' identified by 'centos'"
[root@Centos7 ~]# mysql -e "flush privileges"
[root@Centos7 ~]# mysqldump -A -F --single-transaction --master-data=1 > /data/master.sql
[root@Centos7 ~]# scp /data/master.sql 192.168.37.27:/data
#slave_server
CHANGE MASTER TO
MASTER_HOST='192.168.37.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000011',
MASTER_LOG_POS=245;
[root@slave_sql ~]# vim /etc/my.cnf
[mysqld]
server-id=27
read-only
[root@slave_sql ~]# rm -rf /var/lib/mysql/* #清理旧数据
[root@slave_sql ~]# systemctl restart mariadb
MariaDB [test]> source /data/master.sql
MariaDB [test]> start slave;
MariaDB [test2]> select * from hellodb.students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | a | 20 | F | NULL | NULL |
| 27 | b | 30 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)
MariaDB [test2]> select count(id) from hellodb.testlog;
+-----------+
| count(id) |
+-----------+
| 800000 | #80W条记录都在
+-----------+
1 row in set (0.16 sec)
*主服务器down，提升一个从服务器成为新的主
#再添加1个从服务器,IP 17
#在主上备份数据
[root@Centos7 ~]# mysqldump -A -F --single-transaction --master-data=1 > /data/master2.sql
[root@Centos7 ~]# scp /data/master2.sql 192.168.37.17:/data
#slave2
[root@slave2_sql ~]# vim /etc/my.cnf
[mysqld]
server-id=17
read-only
[root@slave2_sql ~]$ vim /data/master2.sql
CHANGE MASTER TO
MASTER_HOST='192.168.37.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000012',
MASTER_LOG_POS=245;
[root@slave2_sql ~]$ rm -rf /var/lib/mysql/*
[root@slave2_sql ~]$ systemctl restart mariadb
[root@slave2_sql ~]$ mysql < /data/master2.sql
[root@slave2_sql ~]$ mysql -e "start slave;"
#模拟主数据库down机了
[root@Centos7 ~]# iptables -A INPUT -p tcp --dport 3306 -j REJECT
#查看两服务器的二进制日志编号谁最大
#slave2
MariaDB [(none)]> show slave status\G;
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 328
#slave
MariaDB [(none)]> show slave status\G;
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 328
#值都一样，哪就随便找一个
#slave2当新主
[root@slave2_sql ~]$ vim /etc/my.cnf
[mysqld]
server-id=17
log-bin=/data/logs/mysql-bin
[root@slave2_sql ~]$ systemctl restart mariadb
[root@slave2_sql ~]$ mysql -e "show master logs"
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 245 |
+------------------+-----------+
[root@slave_sql ~]# mkdir /data/logs
[root@slave_sql ~]# chown mysql.mysql /data/logs/
#slave
[root@slave_sql ~]# vim /etc/my.cnf
[mysqld]
server-id=27
log-bin=/data/logs/mysql-bin
read-only
[root@slave_sql ~]$ systemctl restart mariadb
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status;
Empty set (0.00 sec)
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.37.17',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=245;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status;
*级联复制
#3台机器：IP master:7 cascad:17 slave:27
#master_server
[root@matser ~]# vim /etc/my.cnf
[mysqld]
server-id=7
log_bin=/data/logs/mysql-bin
binlog_format=row
[root@matser ~]# systemctl restart mariadb
[root@matser ~]# mysqldump -A -F --single-transaction --master-data=1 > /data/master3.sql
[root@matser ~]# scp /data/master3.sql 192.168.37.17:/data
#cascad_server
[root@cascad ~]$ vim /etc/my.cnf
[mysqld]
server-id=17
log-bin=/data/logs/mysql-bin
log_slave_updates
read-only
[root@cascad ~]$ rm -rf /var/lib/mysql/*
[root@cascad ~]$ vim /data/master3.sql
CHANGE MASTER TO
MASTER_HOST='192.168.37.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000014',
MASTER_LOG_POS=245;
[root@cascad ~]$ systemctl restart mariadb
[root@cascad ~]$ mysql < /data/master3.sql
[root@cascad ~]$ mysql -e "start slave"
[root@cascad ~]$ mysql -e "show master logs"
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 264 |
| mysql-bin.000002 | 30334 |
| mysql-bin.000003 | 1038814 |
| mysql-bin.000004 | 23394727 |
+------------------+-----------+
[root@cascad ~]$ mysql -e "flush privileges" #不刷新权限会出现同步失败
[root@cascad ~]$ mysqldump -A -F --single-transaction --master-data=1 > /data/cascad_all.sql
[root@cascad ~]$ scp /data/cascad_all.sql 192.168.37.27:/data
#slave_server
[root@slave_sql ~]# vim /etc/my.cnf
[mysqld]
server-id=27
log-bin=/data/logs/mysql-bin
read-only
[root@slave_sql ~]# rm -rf /var/lib/mysql/*
[root@slave_sql ~]# systemctl restart mariadb
[root@slave_sql ~]# vim /data/cascad_all.sql
CHANGE MASTER TO
MASTER_HOST='192.168.37.17',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=245;
[root@slave_sql ~]# mysql < /data/cascad_all.sql
[root@slave_sql ~]# mysql -e "start slave"
[root@slave_sql ~]# mysql -e "show slave status\G"
#在主上创建、插入操作看是否同步
create database db1;
drop database db1;
半同步复制
#3台机器：IP master:7 slave1:17 slave2:27
#matser_server
MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so'; #安装插件
MariaDB [(none)]> show plugins; #查看插件
MariaDB [(none)]> set global rpl_semi_sync_master_enabled=1; #开启插件
MariaDB [(none)]> show global variables like '%semi%'; #查看变量状态
MariaDB [(none)]> show global status like '%semi%';
[root@matser ~]# vim /etc/my.cnf
[mysqld]
server-id=7
log_bin=/data/logs/mysql-bin
binlog_format=row
auto_increment_offset=1
auto_increment_increment=2
rpl_semi_sync_master_enabled=1
[root@matser ~]# systemctl restart mariadb
[root@matser ~]# mysqldump -A -F --single-transaction --master-data=1 > /data/master4.sql
[root@matser ~]# scp /data/master4.sql 192.168.37.17:/data
#slave1
[root@slave_sql ~]$ rm -rf /var/lib/mysql/*
[root@slave_sql ~]$ systemctl restart mariadb
[root@slave_sql ~]$ vim /data/master4.sql
CHANGE MASTER TO
MASTER_HOST='192.168.37.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000018',
MASTER_LOG_POS=245;
[root@slave_sql ~]$ mysql < /data/master4.sql
[root@slave_sql ~]$ mysql -e "install plugin rpl_semi_sync_slave soname 'semisync_slave.so'" #安装插件
[root@slave_sql ~]$ vim /etc/my.cnf
[mysqld]
server-id=17
read-only
rpl_semi_sync_slave_enabled=1
[root@slave_sql ~]$ systemctl restart mariadb
[root@slave_sql ~]$ mysql -e "start slave"
[root@slave_sql ~]$ scp /data/master4.sql 192.168.37.27:/data/
#slave2
[root@slave2_sql ~]# rm -rf /var/lib/mysql/*
[root@slave2_sql ~]# systemctl restart mariadb
[root@slave2_sql ~]# mysql < /data/master4.sql
[root@slave2_sql ~]# mysql -e "install plugin rpl_semi_sync_slave soname 'semisync_slave.so'"
[root@slave2_sql ~]# vim /etc/my.cnf
[mysqld]
server-id=27
read-only
rpl_semi_sync_slave_enabled=1
[root@slave2_sql ~]# systemctl restart mariadb
[root@slave2_sql ~]# mysql -e "start slave"
#matser_server创建测试
create database db2;
drop database db2;
实现基于SSL的主从复制
#2台机器：IP master:7 slave1:17
#master_server
#生成主证书
[root@matser ~]# mkdir /etc/my.cnf.d/ssl
[root@matser ~]# cd /etc/my.cnf.d/ssl/
[root@matser ssl]# openssl genrsa 2048 > cakey.pem
Generating RSA private key, 2048 bit long modulus
........................................+++
.....+++
e is 65537 (0x10001)
[root@matser ssl]# openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:GD
Locality Name (eg, city) [Default City]:GZ
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:IT
Common Name (eg, your name or your server's hostname) []:ca.magedu.com
Email Address []:
[root@matser ssl]# openssl req -newkey rsa:1024 -days 365 -nodes -keyout master.key > master.csr
Generating a 1024 bit RSA private key
.++++++
.++++++
writing new private key to 'master.key'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:GD
Locality Name (eg, city) [Default City]:GZ
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:IT
Common Name (eg, your name or your server's hostname) []:master.magedu.com
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@matser ssl]# openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt
Signature ok
subject=/C=CN/ST=GD/L=GZ/O=magedu/OU=IT/CN=master.magedu.com
Getting CA Private Key
#生成从证书
[root@matser ssl]# openssl req -newkey rsa:1024 -days 365 -nodes -keyout slave.key > slave.csr
Generating a 1024 bit RSA private key
......++++++
..........................................++++++
writing new private key to 'slave.key'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:GD
Locality Name (eg, city) [Default City]:GZ
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:IT
Common Name (eg, your name or your server's hostname) []:slave.magedu.com
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@matser ssl]# openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > slave.crt
Signature ok
subject=/C=CN/ST=GD/L=GZ/O=magedu/OU=IT/CN=slave.magedu.com
Getting CA Private Key
[root@matser ssl]# vim /etc/my.cnf
[mysqld]
server-id=7
log_bin=/data/logs/mysql-bin
binlog_format=row
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key
[root@matser ssl]# systemctl restart mariadb
MariaDB [(none)]> grant replication slave on *.* to repluser2@'192.168.37.%' identified by 'centos' require ssl;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show variables like '%ssl%';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/my.cnf.d/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /etc/my.cnf.d/ssl/master.crt |
| ssl_cipher | |
| ssl_key | /etc/my.cnf.d/ssl/master.key |
+---------------+------------------------------+
7 rows in set (0.00 sec)
#ssl加密连接测试
[root@matser ssl]# mysql --ssl-ca=cacert.pem --ssl-cert=master.crt --ssl-key=master.key
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 5
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.60-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 9 min 13 sec
Threads: 3 Questions: 34 Slow queries: 0 Opens: 0 Flush tables: 2 Open tables: 26 Queries per second avg: 0.061
--------------
#把证书文件拷贝到slave_server
[root@matser ssl]# scp -r /etc/my.cnf.d/ssl 192.168.37.17:/etc/my.cnf.d/
#slave_server
[root@slave_sql ~]# vim /etc/my.cnf
[mysqld]
server-id=17
read-only
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key
[root@slave_sql ~]# systemctl restart mairadb
MariaDB [(none)]> stop slave;
MariaDB [(none)]> reset slave all;
[root@matser ssl]# mysql -e "show master status"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000020 | 245 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.37.7',
-> MASTER_USER='repluser2',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000020',
-> MASTER_LOG_POS=245,
-> MASTER_SSL=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
基于mysql 5.7的GTID主从复制
#mysql5.7二进制安装，主从都一样操作
[root@matser ~]# tar xf mysql-5.7.26-el7-x86_64.tar.gz -C /usr/local
[root@matser ~]# useradd -r -s /bin/nologin mysql
[root@matser ~]# cd /usr/local
[root@matser local]# ln -s mysql-5.7.26-el7-x86_64/ mysql
[root@matser local]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@matser local]# . /etc/profile.d/mysql.sh
[root@matser local]# mkdir /data/mysql
[root@matser local]# chown mysql.mysql /data/mysql
[root@matser local]# mysqld --initialize --user=mysql --datadir=/data/mysql
[Note] A temporary password is generated for root@localhost: pjac6hh()fQh #密码复制出来
[root@matser local]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[root@matser local]# cp mysql/support-files/mysql.server /etc/init.d/mysqld
[root@matser local]# service mysqld start #启动服务
Starting MySQL.Logging to '/data/mysql/mysql.log'.
SUCCESS!
[root@matser local]# mysqladmin -uroot -p"pjac6hh()fQh" password centos #修改密码
#配置GTID主从
#master_server
[root@matser local]# vim /etc/my.cnf
[mysqld]
server-id=7
log-bin=/data/logs/mysql-bin
gtid_mode=on
enforce_gtid_consistency
[root@matser local]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@matser local]# mysql -uroot -p
mysql> grant replication slave on *.* to 'repluser'@'192.168.37.%' identified by 'centos';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000021 | 614 | | | d7facf80-7314-11ea-8544-000c292d71b8:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#slave_server
[root@slave_sql local]# vim /etc/my.cnf
[mysqld]
server-id=17
gtid_mode=on
enforce_gtid_consistency
[root@slave_sql local]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
mysql> CHANGE MASTER TO MASTER_HOST='192.168.37.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION=1;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000021
Read_Master_Log_Pos: 614
Relay_Log_File: slave_sql-relay-bin.000002
Relay_Log_Pos: 827
Relay_Master_Log_File: mysql-bin.000021
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
1 row in set (0.00 sec)
#主服务器导入数据库测试
[root@matser local]# mysql -uroot -p < ~/hellodb_innodb.sql
proxySQL（读写分离）
#3台机器 7(master_sql) 17(slave_sql) 27(monitor)
#先搭主从
#master_server
[root@matser ~]# service mysqld stop
Shutting down MySQL............ SUCCESS!
[root@matser ~]# rm -rf /etc/my.cnf
[root@matser ~]# echo > /etc/profile.d/mysql.sh
[root@matser ~]# PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin"
[root@matser ~]# yum install mariadb-server -y
[root@matser ~]# vim /etc/my.cnf
[mysqld]
server-id=7
log-bin=/data/logs/mysql-bin
[root@matser ~]# systemctl start mariadb
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000022 | 774 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#注意：搭完从再执行以下语句
MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'192.168.37.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant replication client on *.* to 'monitor'@'192.168.37.%' identified by 'magedu';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant all on *.* to sqluser@'192.168.37.%' identified by 'magedu';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#slave_server
[root@slave_sql ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@slave_sql ~]# rm -rf /etc/my.cnf
[root@slave_sql ~]# echo > /etc/profile.d/mysql.sh
[root@slave_sql ~]# PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin"
[root@slave_sql ~]# yum install mariadb-server -y
[root@slave_sql ~]# vim /etc/my.cnf
[mysqld]
server-id=17
read-only
[root@slave_sql ~]# rm -rf /var/lib/mysql/*
[root@slave_sql ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.37.7', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000022', MASTER_LOG_POS=774;
MariaDB [(none)]> start slave;
#monitor
[root@proxysql ~]# yum install proxysql mariadb -y #注意这里是安装mysql客户端
[root@proxysql ~]# vim /etc/proxysql.cnf
interfaces="0.0.0.0:3306"
[root@proxysql ~]# service proxysql start
Starting ProxySQL: 2020-03-31 15:22:35 [INFO] Using config file /etc/proxysql.cnf
[root@proxysql ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
MySQL [none]> use main
MySQL [none]> select * from sqlite_master where name='mysql_servers'\G #查看字段
MySQL [main]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.37.7',3306);
Query OK, 1 row affected (0.00 sec)
MySQL [main]> insert into mysql_servers(hostgroup_id,hostname,port) values(20,'192.168.37.17',3306);
Query OK, 1 row affected (0.00 sec)
MySQL [main]> load mysql servers to runtime; #加载到内存，立即生效
Query OK, 0 rows affected (0.00 sec)
MySQL [main]> save mysql servers to disk; #保存到磁盘，永久生效
Query OK, 0 rows affected (0.00 sec)
MySQL [main]> set mysql-monitor_username='monitor';
MySQL [main]> set mysql-monitor_password='magedu';
MySQL [main]> load mysql variables to runtime;
MySQL [main]> save mysql variables to disk;
MySQL [main]> insert into mysql_replication_hostgroups values(10,20,"test"); #设置分组
MySQL [main]> insert into mysql_users(username,password,default_hostgroup) values('sqluser','magedu',10); #写组
MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1); #读组
MySQL [main]> load mysql servers to runtime;
MySQL [main]> save mysql servers to disk;
MySQL [main]> load mysql users to runtime;
MySQL [main]> save mysql users to disk;
MySQL [main]> load mysql query rules to runtime;
MySQL [main]> save mysql query rules to disk;
#查询
select * from mysql_server_connect_log; #查看监控连接日志，判断是否正常
select * from mysql_server_ping_log; #查看监控心跳信息
select * from mysql_server_read_only_log; #查看read_only的监控日志
select * from mysql_server_replication_lag_log; #查看replication_lag的监控日志
select hostgroup_id,hostname,port,status,weight from mysql_servers; #查看监控服务器的分组情况
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.37.7 | 3306 | ONLINE | 1 |
| 20 | 192.168.37.17 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
2 rows in set (0.00 sec)
SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC; #查看调度
#测试
[root@proxysql ~]# mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
| 17 |
+-------------+
[root@proxysql ~]# mysql -usqluser -pmagedu test -P6033 -h127.0.0.1 -e 'create table t(id int);'
[root@proxysql ~]# mysql -usqluser -pmagedu hellodb -P6033 -h127.0.0.1 -e 'select * from students'
MySQL [main]> SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+----------------------------------+
| 20 | 11270369 | 3 | select @@server_id |
| 10 | 7314 | 1 | create table t(id int) |
| 10 | 2535 | 1 | show databases |
| 20 | 2324 | 1 | select * from students |
| 10 | 947 | 1 | create table t(id,int) |
| 10 | 0 | 2 | select @@version_comment limit ? |
| 10 | 0 | 1 | select @@version_comment limit ? |
| 10 | 0 | 4 | select @@version_comment limit ? |
+----+----------+------------+----------------------------------+
8 rows in set (0.00 sec)
*MHA（集群，一主多从）
#4台新机器做实验：
#A IP:37.7：mha-master
#B IP:37.17：master
#C IP:37.27：slave1
#D IP:37.37：slave2
#所有机器安装mariadb
yum install mariadb-server -y
#master_sql
[root@master_sql ~]# vim /etc/my.cnf
[mysqld]
server-id=17
log-bin
skip_name_resolve
[root@master_sql ~]# systemctl restart mariadb
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.37.%' identified by 'magedu';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant all on *.* to mhauser@'192.168.37.%' identified by 'magedu';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 682 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
[root@master_sql ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm
#slave1_sql
[root@slave1_sql ~]# vim /etc/my.cnf
[mysqld]
server-id=27
log-bin
read-only
skip_name_resolve
relay_log_purge=0
[root@slave1_sql ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.37.17',
-> MASTER_USER='repluser', MASTER_PASSWORD='magedu',
-> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
[root@slave1_sql ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm
#slave1_sq2
[root@slave2_sql ~]# vim /etc/my.cnf
[mysqld]
server-id=37
log-bin
read-only
skip_name_resolve
relay_log_purge=0
[root@slave2_sql ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.37.17',
-> MASTER_USER='repluser', MASTER_PASSWORD='magedu',
-> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
[root@slave2_sql ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm
#mha_server
[root@mha-master ~]# yum install mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm
[root@mha-master ~]# ssh-keygen
[root@mha-master ~]# ssh-copy-id 192.168.37.7
[root@mha-master ~]# scp -r .ssh/ 192.168.37.17:/root/
[root@mha-master ~]# scp -r .ssh/ 192.168.37.27:/root/
[root@mha-master ~]# scp -r .ssh/ 192.168.37.37:/root/
[root@mha-master ~]# mkdir /etc/mha
[root@mha-master ~]# vim /etc/mha/app1.cnf
#检查
[root@mha-master ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
[root@mha-master ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
#启动服务
[root@mha-master ~]# masterha_manager --conf=/etc/mha/app1.cnf #在前台启动，一次性的，发生故障后把从变主后任务就结束了
[root@mha-master ~]# tail -f /data/mastermha/app1/manager.log #查看日志变化
#master_sql
MariaDB [(none)]> use test
MariaDB [test]> create table testlog (id int auto_increment primary key,name char(10),age int default 20);
MariaDB [test]> delimiter $$
MariaDB [test]>
MariaDB [test]> create procedure sp_testlog()
-> begin
-> declare i int;
-> set i = 1;
-> while i <= 100000
-> do insert into testlog(name,age) values (concat('wang',i),i);
-> set i = i +1;
-> end while;
-> end$$
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]>
MariaDB [test]> delimiter ;
MariaDB [test]>
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| testlog |
+----------------+
1 row in set (0.00 sec)
MariaDB [test]> select sp_testlog(); #执行语句时断电测试
#在mha_master上查看日志，谁变为新主，新主把read-only在配置文件中注释
----- Failover Report -----
app1: MySQL Master failover 192.168.37.17(192.168.37.17:3306) to 192.168.37.27(192.168.37.27:3306) succeeded #可以看到主由37.17变为37.27
Master 192.168.37.17(192.168.37.17:3306) is down!
Check MHA Manager logs at mha-master:/data/mastermha/app1/manager.log for details.
Started automated(non-interactive) failover.
#旧主起来后
[root@master_sql ~]# vim /etc/my.cnf
[mysqld]
server-id=28
log-bin
skip_name_resolve
read-only
[root@master_sql ~]# rm -rf /var/lib/mysql/*
#新主备份数据
[root@slave1_sql ~]# mysqldump -A -F --single-transaction --master-data=1 > /data/new.sql
[root@slave1_sql ~]# scp /data/new.sql 192.168.37.17:/root/
#把旧主改为从服务器
[root@master_sql ~]# systemctl restart mariadb
[root@master_sql ~]# vim /root/new.sql
CHANGE MASTER TO
MASTER_HOST='192.168.37.27',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=245;
[root@master_sql ~]# mysql < /root/new.sql
[root@master_sql ~]# mysql -e "start slave"
*galara cluster（集群，多主读写）
#集群主服务器(至少3台机器)，需要卸载原有mariadb-server
yum remove mariadb-server mariadb mariadb-libs perl-dbd-mysql -y
#安装
[root@mha-master ~]# cat > /etc/yum.repos.d/mysql.repo <<EOF
> [Galera_Cluster]
> name=Galera Cluster
> baseurl=http://yum.mariadb.org/5.5.63/centos7-amd64/
> gpgcheck=0
> EOF
[root@mha-master ~]# yum install MariaDB-Galera-server -y
#修改配置文件
[root@mha-master ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
log-bin
[galera]
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.37.7,192.168.37.17,192.168.37.27"
binlog_format=row
wsrep_cluster_name='cluster'
#配置文件拷贝到其他节点
[root@mha-master ~]# scp /etc/my.cnf.d/server.cnf 192.168.37.17:/etc/my.cnf.d/
[root@mha-master ~]# scp /etc/my.cnf.d/server.cnf 192.168.37.27:/etc/my.cnf.d/
#首次启动服务
[root@mha-master ~]# /etc/init.d/mysql start --wsrep-new-cluster
#其他节点启动
service mysql start
#查看节点数
MariaDB [(none)]> show status like 'wsrep_cluster_size'\G
*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
Value: 3
1 row in set (0.00 sec)
MariaDB [(none)]> show status like 'wsrep_%';
#测试
mysql < hellodb_innodb.sql
#基于mariadb-10.3.22的galera cluster
#安装
[root@mha-master ~]# cat /etc/yum.repos.d/mysql.repo
[mariadb-galera]
name=Galera Cluster
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-10.3.22/yum/centos7-amd64/
gpgcheck=0
[root@mha-master ~]# yum install MariaDB-server -y #自动依赖安装galera
#修改配置文件
[root@mha-master ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
log-bin
[mysqld-safe]
log_error=/var/log/mariadb/mariadb.log
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.37.7,192.168.37.17,192.168.37.27"
binlog_format=row
wsrep_cluster_name='testcluster'
#配置文件拷贝到其他节点
[root@mha-master ~]# scp /etc/my.cnf.d/server.cnf 192.168.37.17:/etc/my.cnf.d/
[root@mha-master ~]# scp /etc/my.cnf.d/server.cnf 192.168.37.27:/etc/my.cnf.d/
#首次启动服务
[root@mha-master ~]# galera_new_cluster
#其他节点启动
systemctl start mysql
网友评论