MySQL数据库运维及集群搭建

一、MySQL运维操作

1、运维管理

• 用户管理：创建、删除、授权

• 密码管理：设置、修改、忘记

• 数据管理：备份与恢复

2、用户管理

• 用户管理：创建（create）、删除（drop）、授权（grant）

  • 管理员用户：'root'@'localhost’

  • 普通用户：管理员创建的用户

1.用户管理
#用户分类：管理员用户、普通用户
#管理员用户：'root'@'localhost'，系统默认创建，能看到所有库、拥有所有权限；
#普通用户：管理员创建的用户，默认只能看到test库，权限来自管理员赋予；

#创建用户（本地用户、远程用户）
mysql> create user '用户名'@'localhost' idntified by '用户密码';
mysql> create user '用户名'@'%' identified by '用户密码';

#管理权限
mysql> grant 用户权限 on 数据库名.数据库表 to '用户名'@'登录地址';
mysql> show grants for '用户名'@'登录地址';
mysql> revoke 用户权限 on 数据库名.数据库表 from '用户名'@'登录地址';

#创建新用户的同时直接授权
mysql> grant 用户权限 on 数据库名.数据库表 to '用户名'@'登录地址' identified by '用户密码';

3、密码管理

（1）设置密码

• 初始化管理员密码，只能设置一次

$ mysqladmin -u用户名 password 密码

• 创建普通用户时设置密码

mysql> create user '用户名'@'登录地址' identified by '用户密码';

（2）修改密码

①更改自己的密码，所有用户都可以使用

mysql> set password = password('新密码');

②管理员用户修改普通用户的密码

mysql> set password for '用户名'@'登录地址'= password("新密码");

#set password方法的封装
mysql> update user set password=password('新密码') where User='用户名' and Host='登录地址';  
mysql> flush privileges;  #或重启服务

（3）忘记密码

①普通用户找管理员重新设置密码

mysql> set password for '用户名'@'登录地址'= password("新密码");

②管理员用户忘记密码

$ vim /etc/my.cnf
        #mysql默认启动区域
        [mysqld] 
    #添加跳过权限表验证
        skip-grant-table  
    
        #mysql安全启动区域，增加了一些安全特性
        #如发现错误时重启服务、将运行时间写入错误日志文件
        #以后使用的趋势，兼容过去的mysqld，也会读取mysqld的配置
        #rpm包的mysql重启服务，默认使用的是安全启动
        [mysqld_safe]         
#重启mysql服务
$ systemctl restart mariadb
$ ss -antp | grep :3306
#直接无密码进入mysql即可
$ mysql    
#数据库重设密码
mysql> update user set password=password('新密码') where User='用户名' and Host='登录地址';    
#退出数据库，把配置文件添加的行去掉，重启数据库即可
$ vim /etc/my.cnf
        #去掉skip-grant-table
$ systemctl restart mariadb

4、数据库备份和还原（无需登录MySQL）

（1）数据文件备份：cp scp rsync

• 备份/var/lib/mysql 或者 /usr/local/mysql/data

#方法一：复制数据文件（源码包、RPM包）
$ cp -a /usr/local/mysql/data  备份目录
$ cp -a /var/lib/mysql  备份目录

（2）mysqldump备份，mysql还原：

• 选项：

$ mysqldump -help
-A(--all-databases)：所有数据库
-B(--databases)：多个数据库
-d(--no-data)：没有数据，只备份表结构
-t(--no-create-info)：没有创建信息，只备份表数据

①备份

• 备份一个数据库，touch 44_$(date +"%y-%m-%d").txt

  • mysqldump -u用户名 -p密码 要备份的数据库名 > 库名-表名-备份人-时间.sql

• 备份一个数据库表

  • mysqldump -u用户名 -p密码 要备份的数据库名 表名 > 库名-表名-备份人-时间.sql

• 备份多个数据库

  • mysqldump -u用户名 -p密码 -B 数据库名1 数据库名2 > 库名-表名-备份人-时间.sql

• 备份所有数据库

  • mysqldump -u用户名 -p密码 -A > 库名-表名-备份人-时间.sql

• 只备份表结构

  • mysqldump -u用户名 -p密码 -d 要备份的数据库名 表名 > 库名-表名-备份人-时间.sql

• 只备份表数据

  • mysqldump -u用户名 -p密码 -t 要备份的数据库名 表名 > 库名-表名-备份人-时间.sql

#备份一个数据库，touch 44_$(date +"%y-%m-%d").txt
$ mysqldump -u用户名 -p密码 要备份的数据库名 > 文件名.sql
#备份一个数据库表
$ mysqldump -u用户名 -p密码 要备份的数据库名 表名 > 文件名.sql
#备份多个数据库
$ mysqldump -u用户名 -p密码 --databases 数据库名1 数据库名2 > 文件名.sql
#备份所有数据库
$ mysqldump -u用户名 -p密码 --all-databases > 文件名.sql

②还原

• 恢复的备份是一个数据库的时候需要手动创建数据库，再指定还原

• 恢复的备份是一个数据表的时候需要指定数据库还原

  • mysql -u用户名 -p密码 要还原到的数据库名 < 文件名.sql

• 还原多个数据库

  • mysql -u用户名 -p密码 < 文件名.sql

（3）二进制日志文件备份、恢复数据

• 修改配置文件，打开二进制日志log-bin

• 记录：对数据有影响的 DDL DML DCL

• 开启二进制日志配置、产生二进制日志

#方法三：二进制日志文件备份、恢复数据
mysql> show variables like '%log%';

#开启二进制日志配置、产生二进制日志
$ vim /etc/my.cnf
        log-bin=mysql-bin
$ systemctl restart mariadb

• 查看二进制日志、恢复数据

#查看二进制日志、恢复数据
$ cd /var/lib/mysql/
#查看二进制日志文件
$ mysqlbinlog mysql-bin.000001
或：
mysql> show binlog events in '二进制文件名';

#指定二进制事件的起止位置进行数据恢复（-u和-p参数根据连接进数据库参数而定）
$ mysqlbinlog --start-position 245 --stop-position 466 mysql1.000001 | mysql -uroot -p123

5、数据库日志

• show variables like '%log%’;

  • 查询日志：general_log，记录所有查询语句

  • 慢查询日志：slow_query_log，显示时间较慢的查询

  • 二进制日志：log_bin，记录改变数据结构的语句

  • 中继日志：relay_log，主从结构中的同步的日志

• #开启通用查询日志（gennral_log）

#开启通用查询日志（gennral_log）
$ vim /etc/my.cnf
        general_log=on
        #指定通用日志文件名，默认在数据文件位置、名字为：主机名.log
        general_log_file=/var/lib/mysql/general.log
$ systemctl restart mariadb

二、MySQL集群搭建

（一）MySQL主从（从）搭建

0、相关知识

（1）原理：mysql主从服务同步是基于二进制日志文件进行的

• 主服务器设置不同的server-id，主服务器打开二进制日志，创建远程用户并授权。

• 从服务器设置不同的server-id，设置同步信息，打开同步。从服务器使用I/O线程去主服务器二进制日志取日志回来，写入从服务器中继日志中，然后使用SQL线程从中继日志中取SQL语句，执行写入数据库。中继日志是同步的核心，连接master和slave。

• 第一次全量同步，从start slave主动去主拉取数据

• 之后，在主未改动时，两边的线程进入休眠状态

• 当主再次改动数据，存入二进制日志，并推送给从服务

（2）步骤：

• 主服务器：在/etc/my.cnf 配置文件中添加：bin-log=mysql-bin，打开二进制日志，添加不同的server-id；登录MySQL，创建远程用户，用于从服务器同步用

• 从服务器：在/etc/my.cnf 配置文件中添加不同的server-id；登录MySQL，执行：change master to ……设置同步，start slave；开启同步。

（3）数据同步特点：单向

• 数据是单向同步的，默认从可以写入但不会同步到主

• 同步的数据默认是所有库下的所有表

• 同步的用户最小权限是replication slave，且需设在*.*上(只有两个数据库同步；test和information)

（4）每次重启服务就会生成一个新的二进制日志文件

（5）查看从服务器同步状态

mysql> show slave status\G;

（6）同步状态中I/O错误

• 主从服务器的server-id可能相同了

• 同步的不是*.*所有库所有表

• 解决方法：

  • 改不同的server-id

  • 只能同步 *.*

（7）同步状态中SQL错误

• 解决方法：

  • 跳过错误SQL。重新change master to ……+新的偏移量

  • 手工处理错误SQL（比如没库可以创建等等）

• 重新同步：stop slave; start slave;

（8）以上错误都会使同步停止，需重新同步

（9）设置远程用户同步的数据库只能是*.*所有库所有表。权限可以是 all（太大），也可以是 replication slave 只进行同步（最小权限，只有两个数据库同步；test和information）

（10）不同步某个库

• 在从配置文件 /etc/my.cnf 中添加: replicate-ignore-db=库名

（11）同步某个库

• 在从配置文件 /etc/my.cnf 中添加: replicate-do-db=库名

（12）注意测试方法

• 如果从服务器做了不同步某个库，那么在主服务器操作数据表时一定要切换到要操作表的数据库中。因为二进制日志在记录SQL语句时默认会在前面加上use database，若不切换数据库，直接使用 database.table 操作不同步数据库时，日志记录的 use database; 还是当前可同步数据库名，而从服务器则是以 use database; 来判断后面的SQL是否要执行的。

1、主服务器

#安装mysql、启动服务
$ yum -y install mariadb mariadb-server

#配置主配置文件：打开二进制日志、设置id值
$ vim /etc/my.cnf
        character-set-server=utf8
        log-bin=mysql-bin
        server-id=1
$ systemctl restart mariadb
#初始化密码，可选设置
$ mysqladmin -uroot password 123

#创建用于同步的用户、并授权
$ mysql -uroot -p123
mysql> grant all on *.* to 'rsyncer'@'%' identified by '123';
#查看当前服务器的日志文件及偏移量
mysql> show master status;

• 配置主配置文件：打开二进制日志、设置id值

• 必须设置不同的server-id，否则主从服务不能启动

• 创建远程用户并授权，查看当前日志文件及偏移量

mysql> grant all on *.* to 'rsyncer'@'%' identified by '123'; #查看当前服务器的日志文件及偏移量 mysql> show master status;

• 查看日志文件内容

mysql> show binlog events in'mysql-binlog.000001';

2、从服务器

#安装mysql、启动服务
$ yum -y install mariadb mariadb-server

#配置主配置文件：设置id值
$ vim /etc/my.cnf
        server-id=2
$ systemctl restart mariadb
#初始化密码，可选设置
$ mysqladmin -uroot password 123

#设置同步
$ mysql -uroot -p123
mysql> change master to master_host='主服务器IP',master_user='用户',master_password='用户密码',master_log_file='二进制文件',master_log_pos=偏移量;

change master to master_host='192.168.40.44',master_user='tb',master_password='123',master_log_file='mysql-bin.000003',master_log_pos=245;

#开启同步
mysql> start slave;

#查看同步状态
mysql> show slave status\G;

#若从的IO线程或者sql线程不是yes，可以关闭同步，重新做change master to;
mysql> stop slave;

结果注：
1.若IO线程是no，则同步的用户出了问题；
#同步用户的最小权限是replication slave,即只有复制二进制日志的权限；
#同步用户需要设置在所有库.所有表上，保证有权限拿走对应的二进制日志；
mysql> grant replication slave on *.* to 'rsyncer'@'%' identified by '123';
2.若sql线程是no，则同步的数据出了问题；

• I/O错误：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it)

3、测试

1.打开salve同步时,在主服务器内创建数据库、插入数据，在从服务器上可以查询到；

2.关闭slave后，在主服务器上的操作，在从服务器查询不到，两个服务器数据不一致；再打开同步（start slave），数据同步了。

（二）MySQL主主搭建

1、问题:自增id冲突

如果同时在两台服务器中对同一个数据库下的同一个数据表写入数据时自增id可能会冲突

2、解决:修改id自增值

双方vim /etc/my.cnf
一个设置:
auto-increment-offset=1auto-increment-increment=2
另一个设置:
auto-increment-offset=2auto-increment-increment=2
offset:id起始值
increment:增加量
这样设置后，双方的自增id会变为一个1、3、5、7、9奇数增长
另一个为2、4、6、8偶数增长
避免了id相同的冲突

3、主1服务器

#安装mysql、启动服务
$ yum -y install mariadb mariadb-server
#配置主配置文件：打开二进制日志、设置id值
$ vim /etc/my.cnf
        log-bin=mysql-bin
        server-id=11
        
        #设置同步参数
        #设置要同步的数据库，如果是主从，在从的一方写
        replicate-do-db=test  
        #忽略、不同步的数据库(数据库名后不要有空格)
        replicate-ignore-db=mysql
        replicate-ignore-db=information_schema 

        
         #主键的每次增长值、初始值
        auto-increment-increment=2
        auto-increment-offset=1 
$ systemctl restart mariadb
$ mysqladmin -uroot password 123

#创建用于同步的用户、并授权
$ mysql -uroot -p123
mysql> grant all on *.* to 'rsyncer1'@'%' identified by '123';
#查看当前服务器的日志文件及偏移量
mysql> show master status;

#作为主2服务器的从服务器
mysql> change master to master_host='主2服务器IP',master_user='rsyncer2',master_password='123',master_log_file='二进制文件',master_log_pos=偏移量;

change master to master_host='192.168.66.25',master_user='slave',master_password='123',master_log_file='mysql-bin.000002',master_log_pos=1649;

mysql> start slave;
mysql> show slave status\G;

4、主2服务器

#安装mysql、启动服务
$ yum -y install mariadb mariadb-server
#配置主配置文件：打开二进制日志、设置id值
$ vim /etc/my.cnf
        log-bin=mysql-bin
        server-id=12
        
        #设置同步参数,从主的二进制日志中拿到从的中继日志的时候
        #设置要同步的数据库
        replicate-do-db=test  
        replicate-do-db=test 
        #忽略、不同步的数据库 
        replicate-ignore-db=mysql 
        replicate-ignore-db=information_schema 
        
         #主键的每次增长值、初始值
        auto-increment-increment=2
        auto-increment-offset=2
$ systemctl restart mariadb
$ mysqladmin -uroot password 123

#创建用于同步的用户、并授权
$ mysql -uroot -p123
mysql> grant all on *.* to 'rsyncer2'@'%' identified by '123';
#查看当前服务器的日志文件及偏移量
mysql> show master status;

#作为主1服务器的从服务器
mysql> change master to master_host='主1服务器IP',master_user='rsyncer1',master_password='123',master_log_file='二进制文件',master_log_pos=偏移量;
mysql> start slave;
mysql> show slave status\G;

测试

在两个服务器的任何一边进行操作，都会同步到对方；

（三）MySQL读写分离

1、主服务器

#安装mysql、启动服务
$ yum -y install mariadb mariadb-server
#配置主配置文件：打开二进制日志、设置id值
$ vim /etc/my.cnf
        log-bin=mysql-bin
        server-id=1
$ systemctl restart mariadb
$ mysqladmin -uroot password 123

#创建用于同步的用户、并授权
$ mysql -uroot -p123
mysql> grant all on *.* to 'rsyncer'@'%' identified by '123';

#查看当前服务器的日志文件及偏移量
mysql> show master status;

2、从服务器

#安装mysql、启动服务
$ yum -y install mariadb mariadb-server
#配置主配置文件：打开二进制日志、设置id值
$ vim /etc/my.cnf
        log-bin=mysql-bin
        server-id=2
$ systemctl restart mariadb
$ mysqladmin -uroot password 123

#设置同步
$ mysql -uroot -p123
mysql> change master to master_host='主服务器IP',master_user='用户',master_password='用户密码',master_log_file='二进制文件',master_log_pos=偏移量;

change master to master_host='192.168.66.24',master_user='replicator',master_password='123',master_log_file='mariadb-bin.000003',master_log_pos=377;

#开启同步
mysql> start slave;

#查看同步状态
mysql> show slave status\G;

3、搭建中间Amoeba

#安装jdk，因为Amoeba是java语言写的,运行时一定需要JAVA_HOME的环境变量
$ java -version
#安装jdk指定版本、覆盖系统默认安装的
$ tar -zxf jdk-8u361-linux-x64.tar.gz
$ cp -a jdk1.8.0_361 /usr/local/jdk8
#配置环境变量,注意默认安装的jdk在/usr/bin下
$ vim /etc/profile      
        export JAVA_HOME=/usr/local/jdk8
        export JAVA_BIN=$JAVA_HOME/bin 
        export PATH=$JAVA_BIN:$PATH 
#让配置文件生效
$ source /etc/profile
#测试java环境，查看版本
$ java -version    

#安装Amoba
$ mkdir /usr/local/amoeba
$ unzip amoeba-mysql-1.3.1-BETA.zip -d /usr/local/amoeba/
$ cd /usr/local/amoeba

#修改bin下的amoeba的DEFAULT_OPTS，支持64位  --58行
$ vim bin/amoeba
        #DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss128k"
        DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"
#修改conf配置文件amoeba.xml，配置各服务

$ vim conf/amoeba.xml
        #配置上层调用amoeba的配置
        <server>
                <property name="port">8066</property>
                <property name="ipAddress">192.168.66.13</property>
                <property name="user">root</property>
                <property name="password">123</property>
        </server>
        #配置进程信息
         <connectionManagerList>
        <connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
                 <property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>
                 #取消下行注释
            <property name="processors">5</property>
         </connectionManager>
     </connectionManagerList>

        #配置mysql服务器（ip地址、连接用户）
        <dbServerList>
        <dbserver name="server1">
                        <property name="port">3306</property>
                        <property name="ipAddress">192.168.66.22</property>
                        <property name="schema">test</property>
                        <property name="user">amoeba</property>
                        <property name="password">123</property>
        </dbserver>
        <dbserver name="server2">
                        <property name="port">3306</property>
                        <property name="ipAddress">192.168.66.23</property>
                        <property name="schema">test</property>
                        <property name="user">amoeba</property>
                        <property name="password">123</property>
        </dbserver>

        #分配mysql服务的负载情况(有多个读时需设置)
        <dbserver name="master" virtual="true">
                        <poolConfig class="">
                                <property name="loadbalance">1</property>
                                <property name="poolNames">server1</property>
                        </poolConfig>
        </dbserver>
        <dbserver name="slave" virtual="true">
                        <poolConfig class="">
                                <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->  
                                <property name="loadbalance">1</property>
                                <property name="poolNames">server2</property>
                        </poolConfig>
        </dbserver>
        <dbServerList>
        #分配默认池、读写池
        <queryRouter class="">
                <property name="defaultPool">master</property>
        <property name="writePool">master</property>
        <property name="readPool">slave</property>
        </queryRouter>
        
#保存配置文件，启动amoeba
#方法一：脱离终端的后台运行，日志会输出在当前目录下的nohup.out文件里
$ nohup bash -x /usr/local/amoeba/bin/amoeba &
#方法二：chmod运行，会把部分输出日志打在控制台上
$ chmod +x /usr/local/amoeba/bin/amoeba 
$ /usr/local/amoeba/bin/amoeba start &

#关闭amoeba
$ kill amoeba的进程号

其中，nohup是不挂起的意思
含义：该命令会在你退出账号或关闭终端后仍继续运行相应的进程；
格式：nohup command &
结果：会把命令日志输出到当前目录的nohup.out文件里