一、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复制的环境准备(连不上的排错思路):
- mysql复制最好确保master和slave服务器上的mysql版本相同(如果不能满足版本一致,那么就要保证master主节点的版本低于slave节点的版本)
- master和slave两节点需要时间同步
- 网络互通,ping测试
- master一定要开启binlog二进制日志功能(通常为了数据安全考虑,slave也开启binlog功能)
- 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 /结果限定/