Mysql数据库安装，基本命令,主从复制,读写分离,数据库的冷热备和数据库的还原，慢查询日志打开

1. 安装Mysql数据库

使用二进制安装Mysql数据库

useradd -r -s /sbin/nologin mysql
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci\
-DWITH_MYISAM_STOREAGE_ENGINE=1 \
-DWITH_INNOBASE_STIRAGE_ENGINE=1 \
-DWITH_MEMEORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 \
-DMYSQL_USER=mysql -DMYSQL_TCP_PORT=3306
make && make install
参数详解:
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql      Mysql安装路径
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock       socket接口文件路径
-DEXTRA_CHARSETS=all    扩展字符集支持
-DDEFAULT_CHARSET=utf8  默认字符集设置
-DDEFAULT_COLLATION=utf8_general_ci   默认字符校对
-DWITH_MYISAM_SOTRAGE_ENGINE=1   安装MYISAM存储引擎
-DWITH_INNOBASE_STOREAGE_ENGINE=1 安装innoDB存储引擎
-DWITH_MEMORY_STORAGE_ENGINE=1 安装memory存储引擎
-DWITH_READLINE=1 支持Readline库
-DENABLED_LOCAL_INFILE=1 加载本地数据
-DMYSQL_USER=mysql 指定mysql启动用户名
-DMYSQL_TCP_PORT=3306 指定mysql端口号

ps: 授权用户
chown -R mysql /usr/local/mysql/data
生成Mysql配置文件
cp -a /root/LNMP/mysql-5.5.48/support-files/my-medium.cnf /etc/my.cnf
ps: 初始化数据库
cd /usr/local/mysql
./scripts/mysql_install_db --user=mysql
出现两个OK表示初始化成功标志

ps: 生成mysql自启动脚本
cd /root/LNMP/mysql-5.5.48/support-files
cp -a mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
systemctl mysqld start
ln -s /usr/local/mysql/bin/* /usr/local/bin/

ps: 为mysql设置密码
mysqladmin -uroot password 123456

使用yum安装Mysql

如果使用Centos6版本,默认镜像文件中含有mysql包,直接mysql数据库即可

yum install mysql-server mysql-devel

如果使用Centos7版本,默认镜像文件中没有mysql包,只有mariadb包

yum install -y mariadb-server mariadb-devel

我这里Centos7版本,使用在中国科大的开源镜像站中的mysql包独立安装

ps: 如果地址失效,因为mysql小版本更新很快原因,只需进入http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-5.7/中查看版本号替换下面4个URL即可.

wget http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-5.7/mysql-community-server-5.7.31-1.el7.x86_64.rpm
wget http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-5.7/mysql-community-client-5.7.31-1.el7.x86_64.rpm
wget http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-5.7/mysql-community-common-5.7.31-1.el7.x86_64.rpm
wget http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-5.7/mysql-community-libs-5.7.31-1.el7.x86_64.rpm
# 安装依赖包
yum install libaio perl net-tools -y 
# 卸载mariadb-libs库文件以免和mysql产生冲突
yum remove -y mariadb-libs

安装mysql服务

yum install -y *

# 启动mysql
systemctl start mysqld
netstat -anpt
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      1091/mysqld 
# 直接输入mysql进入
[root@localhost mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye

设置mysql密码,默认为空

[root@localhost mysql]# mysqladmin -uroot password zZ@8875589
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@localhost mysql]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost mysql]# mysql -uroot -pzZ@8875589
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>quit

2.mysql的常用命令

# 创建数据库
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
# 进入数据库
mysql> use school;
Database changed
# 创建表int或者varchar这些数据类型后面可以添加约束条件
# 数据类型有数据性，字符串，时间类型
# 约束条件有not null，PRI主键约束；UNI唯一约束；MUL可重复等
mysql> create table student(id int, name varchar(20), grade varchar(20));
Query OK, 0 rows affected (0.02 sec)
# 显示数据库的所有表
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)
# 显示表结构
mysql> desc student；
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| grade | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 第一种 insert into 表名（列名1，列名2，列名3）values (值1,值2);
# 第二种 insert into 表明 values(数据1，数据2...)
mysql> insert into student values(1,'zhangsan',80);
Query OK, 1 row affected (0.00 sec)
# 显示表中所有内容
mysql> select * from student;
+------+----------+-------+
| id   | name     | grade |
+------+----------+-------+
|    1 | zhangsan | 80    |
+------+----------+-------+
1 row in set (0.00 sec)
# 显示表中指定id的内容
mysql> select * from student where id=1;
+------+----------+-------+
| id   | name     | grade |
+------+----------+-------+
|    1 | zhangsan | 80    |
+------+----------+-------+
1 row in set (0.00 sec)
# 向表中插入多条数据
mysql> insert into student values(2,'lisi',30),(3,'wangwu',50);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+------+----------+-------+
| id   | name     | grade |
+------+----------+-------+
|    1 | zhangsan | 80    |
|    2 | lisi     | 30    |
|    3 | wangwu   | 50    |
+------+----------+-------+
3 rows in set (0.00 sec)
# 更新数据把张三的分数80修改为60分
mysql> update student set grade=60 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student where id=1;
+------+----------+-------+
| id   | name     | grade |
+------+----------+-------+
|    1 | zhangsan | 60    |
+------+----------+-------+
1 row in set (0.00 sec)
# 修改mysql管理员密码
mysql>  update mysql.user set authentication_string=password("zZ@8875589") where user="root";
Query OK, 3 rows affected, 1 warning (0.00 sec)
Rows matched: 4  Changed: 3  Warnings: 1
# 删除表中内容
mysql> select * from school.student where id=1;
+------+----------+-------+
| id   | name     | grade |
+------+----------+-------+
|    1 | zhangsan | 60    |
+------+----------+-------+
1 row in set (0.00 sec)

mysql> delete from school.student where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from school.student;
+------+--------+-------+
| id   | name   | grade |
+------+--------+-------+
|    2 | lisi   | 30    |
|    3 | wangwu | 50    |
+------+--------+-------+
2 rows in set (0.00 sec)
# 清空列表
mysql> delete from student;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

3.慢查询开启

mysql> show variables;
ERROR 1682 (HY000): Native table 'performance_schema'.'session_variables' has the wrong structure

从mysql5.7.6开始information_schema.global_status已经始被舍弃，为了兼容性，此时需要打开 show_compatibility_56

mysql> show variables like '%slow%';
+---------------------------+-----------------------------------+
| Variable_name             | Value                             |
+---------------------------+-----------------------------------+
| log_slow_admin_statements | OFF                               |
| log_slow_slave_statements | OFF                               |
| slow_launch_time          | 2                                 |
| slow_query_log            | OFF                               |
| slow_query_log_file       | /var/lib/mysql/localhost-slow.log |
+---------------------------+-----------------------------------+
5 rows in set (0.00 sec)

# 开启mysql慢查询
vim /etc/my.cnf
mysld字段下添加
slow_query_log=1
long_query_time=0.3
# 退出查看slow变量
mysql> show variables like '%slow%'
+---------------------------+-----------------------------------+
| Variable_name             | Value                             |
+---------------------------+-----------------------------------+
| log_slow_admin_statements | OFF                               |
| log_slow_slave_statements | OFF                               |
| slow_launch_time          | 2                                 |
| slow_query_log            | ON                                |
| slow_query_log_file       | /var/lib/mysql/localhost-slow.log |
+---------------------------+-----------------------------------+
5 rows in set (0.00 sec)

4.mysql数据库的冷备

# 使用tar,cp,scp等命令保存mysql日志及程序
tar -jcf 2021-5-1.tar.bz2 /var/lib/mysql
tar -cvf 2021-5-1.tar.bz2 /var/log/mysqld.log

5.数据库热备使用Xtrabackup

# 安装xtrabackup依赖的文件
yum install -y  https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm
yum install -y yum-utils
yum repolist all | grep mysql
# 关闭8.0版本开启5.7版本
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community
yum  install mysql-community-libs-compat -y
# 安装percona源
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
# yum安装
yum -y install percona-xtrabackup-24.x86_64
# 验证安装是否成功
rpm -ql percona-xtrabackup-24 

6.使用Xtrabackup完整备份还原数据

innobackupex --user=root --password='zZ@8875589' /mysql_backup/full
ls /mysql_backup/full
ls /xtrabackup/full/2021-05-01_00-00-02/
# 恢复数据
# 生成回滚日志
innobackupex --apply-log /xtrabackup/full/2021-05-01_00-00-15/
# 恢复文件
innobackupex --copy-back /xtrabackup/full/2021-05-01_00-00-15/

7.使用Xtrabackup增量备份还原数据

innobackupex --user=root --password='QianFeng@123' 
--incremental /mysql_backup
--incremental-basedir=/mysql_backup/2021-05-01_00-00-04
ls  /mysql_backup/
2021-05-01_00-00-04  
2021-05-01_00-00-58
mysql -uroot -p'zZ@8875589' -e  'insert into testdb.test values (3)'
innobackupex --user=root --password='QianFeng@123' 
--incremental /xtrabackup/ 
--incremental-basedir=/xtrabackup/2021-05-01_00-00-26
basedir
#基于周2的备份，会生成一个今天的。
ls  /xtrabackup/
2021-05-01_00-00-04  
2021-05-01_00-00-58
2021-05-01_00-00-36
# 数据恢复
innobackupex --apply-log --redo-only /mysql_bakcup/2017-09-01_00-00-04
#回滚合并
innobackupex --apply-log --redo-only /xtrabackup/2021-05-01_00-00-04 --incremental-dir=/xtrabackup/2021-05-01_00-00-26

8.mysqldump实现数据备份

mysqldump -p'zZ@8875589' \
--all-databases --single-transaction \
--master-data=2 \
--flush-logs \
>  /backup/`date +%F-%H`-mysql-all.sql

参数详解：
–all-databases, -A： 备份所有数据库
–databases, -B： 用于备份多个数据库，如果没有该选项，mysqldump把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldum把每个名字都当作为数据库名。
–force, -f：即使发现sql错误，仍然继续备份
–host=host_name, -h host_name：备份主机名，默认为localhost
–no-data, -d：只导出表结构
–password[=password], -p[password]：密码
–port=port_num, -P port_num：制定TCP/IP连接时的端口号
–quick, -q：快速导出
–tables：覆盖 --databases or -B选项，后面所跟参数被视作表名
–user=user_name, -u user_name：用户名

9.mysqldump数据库的恢复

• 完整恢复数据

mysql -p'zZ@8875589' < /backup/2021-05-02-04-mysql-all.sql
mysql -p'zZ@8875589' -e 'flush privileges'

• 二进制日志恢复

vim /backup/2021-05-02-14-mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.0000010', MASTER_LOG_POS=154;
mysqlbinlog localhost-bin.000002 localhost-bin.000003
 --start-position=154  | mysql -p'zZ@8875589'
# 注意后续有多少日志，要跟多少日志名字。

10.Mysql的主从复制读写分离

1. 准备两台mysql服务器
   Master : 192.168.126.135
   Slave : 192.168.126.136
2. 主服务开启bin-log日志

# Master配置
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin

3. 从服务器设置ID号即可

# Slave配置
# ID不能相同
vim /etc/my.cnf
server-id=2

4. 主库准备测试内容

mysql> select * from student;
+------+----------+-------+
| id   | name     | grade |
+------+----------+-------+
|    1 | zhangsan |    69 |
|    2 | lisi     |    80 |
+------+----------+-------+
2 rows in set (0.00 sec)

5. 主库创建用于从库同步账号

mysql> grant replication slave on *.* to 'tongbu'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.10 sec)
# grant 授权，replication 拷贝，slave 从服务， *.* 所有库下的所有表, tongbu 用户名, % 所有远程IP
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      999 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

6. 从库命令

mysql> change master to master_host='192.168.126.135',master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=999;
Query OK, 0 rows affected, 2 warnings (0.41 sec)
mysql> slave start;
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 出现两个线程Yes代表成功
# 如果有No，就查看mysql日志，/var/log/mysqld.log