Mysql的常用操作

一、MySQL复制（主从、主主）

（一）简介

1、mysql复制（概念）：将mysql数据库的数据分布到多个系统上

（1）以一台服务器作为主服务器，然后将主服务器数据库的数据复制到其他服务器上（slave）

（2）主服务器将更改(包括更新，删除）写入到二进制日志文件，然后将更新的记录发送到其他服务器进行更新（不包括删除）

2、mysql支持的复制类型：

（1）基于语句的复制：在主服务器上执行sql语句，在从服务器上执行一遍

（2）基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍

（3）混合类型的复制：默认采用基于语句的复制，一旦基于语句无法精准复制时，就采用基于行的复制（并不是两种复制类型同时运行，而是采用待命模式）

3、mysql复制解决的问题（作用）

（1）数据分布（data distribution），实现数据的备份（backup）,最终保证数据安全

（2）实现读写分离，缓解数据库的压力。达到负载均衡（load balancing）的效果

4、mysql（主从）复制的原理

（1）master（主）服务器将数据的改变记录二进制binlog日志。

（2）slave（从）服务器会在一定时间间隔对master二进制日志进行探测其是否发生改变；如果发生改变，则开始I/O Thread(进程)请求master二进制事件

（3）主节点为每个I/O线程启动一个dump线程，用于向其发生二进制事件，然后保存到从节点（从服务器）本地的中继日志文件中（二进制binlog日志文件，和中继日志文件可以类比为中转站。你要把东西寄出去，得先到快递点寄出去，然后发到另一个快递站点，别人到快递点拿）

（4）从节点启动SQL线程从中继日志中读取二进制日志，在本地释放，使得其数据和主节点的保持一致

（5）最后：I/O Thread(线程)和SQL Thread将加入睡眠状态，等待下一次被唤醒

备：主主其实就是主从模式的切换（轮流换岗嘛）

5、mysql复制的环境准备（连不上的排错思路）：

1. mysql复制最好确保master和slave服务器上的mysql版本相同（如果不能满足版本一致，那么就要保证master主节点的版本低于slave节点的版本）
2. master和slave两节点需要时间同步
3. 网络互通，ping测试

1. master一定要开启binlog二进制日志功能(通常为了数据安全考虑，slave也开启binlog功能)
2. slave开启两个线程：IO线程和SQL线程（IO/线程复制读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave 的数据库里，这样就能保证slave数据和master数据保持一致）

 （二）主从复制和主主复制

1、mysql复制的模式：

（1）主从复制：主库写数据后，从库会自动同步过来（从库跟着主库变，但是从库发生了改变主库不会收到影响。单向）

（2）主主复制：任何一方发生改变，另一方就会跟着改变（互为主从）

2、主从优点

（1）实现数据备份

（2）基于数据备份，可以实现故障转移（如果主服务器出现了，可以快速切换到从服务器提供服务）

（3）基于数据备份，可以读写分离（主服务器复制写，从服务器负责读，从而实现负载均衡嘛）

（三）主从部署

思路：

①部署master

②授权slave

③查看master状态

④部署slave

⑤指定master

⑥查看同步状态

⑦验证同步

（1）第一步

（2）第二步：

（3）第三步：

 （4）第四步：

（5）第五步：

（6）第六步：

二、人工备份：导入导出

 （一）首先查看mysql的相关目录（whereis 可以查看mysql结尾的文件路径；mariadb和myslq的目录是一致的）

命令：where is mysql

查询结果：

/usr/bin/mysql----   mysql的运行路径

/etc/mysql

/usr/lib/mysql-----   mysql的安装路径

/usr/bin/X11/mysql

/usr/share/mysql

/usr/share/man/man1/mysql.1.gz

/var/lib/mysql --------mysql数据库data文件的存放路径

（二）导出数据库

1、导出数据库作用：可以作为备份，可以应对删库

2、如何导出数据数据：每周定时导出一次

3、具体的步骤：

第一种：在运行目录下执行

1）导出数据和表结构：

mysqldump -u用户名 -p 数据库名 > 数据库名.sql

例如：mysqldump -uroot -p dbname > dbname .sql             //之后会弹出来让你输入密码

拓展：备份所有的数据库

$ mysqldump -u root -p --all-databases > database_dump.txt

第二种：只导出表结构

mysqldump -u用户名 -p -d 数据库名 > 数据库名.sql          //密码之后弹出来

例如：mysqldump -uroot -p -d dbname > dbname .sql

（三）导入数据库

（1）导入数据库常用场景：部分数据库库被误删的情况下可以作为应急

（2）具体的步骤：

第一种：mysqldump -u用户名 -p 数据库名 < 数据库名.sql

例如：mysqldump -uroot -p dbname < dbname .sql

备注：如果导入数据报错什么原因，解决措施是什么

可能是因为数据可能很久没更新，导致报错；

可以加个-R强制导入，mysql -u用户名 -p 数据库名 -R < 数据库名.sql

第二种：

（1）首先建空数据库
mysql>create database abc ;

（2）选择数据库                        

mysql>use abc ;                     //abc 是新建的空数据库

（3）设置数据库编码

mysql>set names utf8;       

（4）导入数据（注意sql文件的路径）

mysql>source /usr/bin/user .sql;

三、mysql多实例

（一）基本概念

1、什么是实例：实例是‘’运行内存‘’和“后台进程”（由cpu处理）的集合，用于处理数据、

2、数据库    ：数据库里面存放的是数据。

实例和数据库的关系：

数据库用于存放数据，实例用来处理数据。如果把数据库用来比作一架飞机，那实例就是飞机的发动机一个实例可以用于一个数据库，多个实例也可以同时用于一个数据库。就像一台发动机可以驱动飞机，两台发动机也可以同时驱动一架飞机。

3、mysql的多实例：就是将一台服务器上同时开启多个不同的服务端口（3306,3307）,并同时运行多个Mysql服务进程，这些服务进程通过不同socket监听不同的服务端口来提供服务。

（1）mysql多实例的好处：当（网站）访问量过大的时候，可以加快数据的运转数据；充分利用服务器空余的cpu,内存等资源，让没有事情可做的cpu,内存等资源动起来，充分利用服务器的资源；

（2）mysql多实例的坏处：但某个数据实例并发很高的时候，就会出现资源互相抢占现象

拓展：当服务器资源充分的时候，很多网络服务器都是可以配置实例的，例如nginx apache redis都可以配置多实例

（3）实现的原理：使用配置文件来启动不同的进程来实现对同一个应用程序（mysql）的处理。在提供服务时，多实例mysql在逻辑上起来是各自独立的，他们根据配置文件的对应设定值，获得服务器响应数据的硬件资源

（二）mysql多实例配置方案

环境准备：先关闭mariadb服务，因为此时是单实例（单进程）

（一）第一步：

（1）创建多实例的目录

  mkdir -p /mysql/data{3306,3307}

（2）创建一个叫mysql的系统用户(在安装软件包的时候可能会创建好了)

  useradd -r mysql

（3）将多实例的目录所有者设置为mysql

chown -R mysql:  /mysql/data/3306

chown -R mysql:  /mysql/data/3307

（二）第二步：初始化数据库

当mysql的系统库（mysql系统库）发生故障或需要新加一个mysql实例时，需要初始化mysql数据库

mysql_install_db  --datadir=/mysql/data3306 --user=mysql

mysql_install_db  --datadir=/mysql/data3307 --user=mysql      (用哪个用户去初始化化数据库，初始化后的数据库文件存放在哪个路径下)

（三）第三步：配置mysql配置文件

 

vim /etc/my.cnf.d/mysql_multi.cnf

[mysql_multi]

mysqld=/usr/bin/mysqld_safe

mysqladmin=/usr/bin/mysqladmin

user=mysql

[mysqld3306]

socket=/tmp/mysql3306.sock             ##为mysql客户程序与服务器之间的本地通信指定一个套接字文件

port=3306`                            #为mysql程序指定TCP/IP通信端口（一般是3306和3307）

pid-file=/mysql/data3306/3306.pid       ##为mysqld程序指定一个存放进程ID的文件 （基本的三要素）

datadir=/mysql/data3306               ##指定读取数据库文件的路径

log=/mysql/log/mysql3306.log          ##会自动创建，可以不指定

user=mysql                          ##指定一个启动程序的mysql用户

[mysqld3307]

socket=/tmp/mysql3307.sock

port=3307

pid-file=/mysql/data3307/3307.pid

datadir=/mysql/data3307

log=/mysql/log/mysql3307.log

user=mysql

备注：也可以用两个文件去配置，3306一个文件，3307一个配置文件

（四）第四步：启动数据库的实例(使mysql的配置生效)

mysqld_multi --defaults-extra-file=/etc/my.cnf.d/mysql_multi.cnf start 3307,3308      

（五）最后一步：检验

ss -ntulp | grep 3306

ss -ntulp | grep 3307

如果能看到这个端口，就证明ok

登录测试：mysql -S /data/3306/mysql.sock

          mysql -S /data/3307/mysql.sock

 

四、查数据

查找格式：

select 列名 from 表名

[WHERE -> GROUP BY -> HAVING -> ORDER BY]

备注：

SELECT selection_list /要查询的列名称/

FROM table_list /要查询的表名称/

WHERE condition /行条件/

GROUP BY grouping_columns /对结果分组/

HAVING condition /分组后的行条件/

ORDER BY sorting_columns /对结果分组/

LIMIT offset_start, row_count /结果限定/