10.16作业 mysql

1、如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点（写出操作步骤）

#（如有创建已创建号的复制权限的账号就不需要创建，否则先创建复制权限的用户）
[root@centos7 ~]#mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.65-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)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'centos';

#首先在主节点做完全备份,并发送到要新建的从节点
[root@centos7 ~]#mysqldump -uroot -p123456 -A -F -q --single-transaction --master-data=1 |gzip > backup_all.sql.gz
[root@centos7 ~]#scp -r backup_all.sql.gz 10.0.0.17:


#在从节点，将收的到的压缩包解压
[root@centos7 ~]#gunzip backup_all.sql.gz

#在从节点安装与主节同版本的mariadb，本配置从节点配置文件
[root@centos7 ~]#yum -y install mariadb-server
[root@centos7 ~]#cat /etc/my.cnf.d/server.cnf
....
[mysqld]
server-id=17
read-only
....

#修改完全备份文件
[root@centos7 ~]#cat backup_all.sql
....
CHANGE MASTER TO
MASTER_HOST='10.0.0.7', 
MASTER_USER='repluser', 
MASTER_PASSWORD='centos', 
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=245
....

#启动mariadb, 关闭日志，还原并启动线程
[root@centos7 ~]#systemctl start mariadb
[root@centos7 ~]#mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.65-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)]> set sql_log_bin=off;
MariaDB [(none)]> source  backup_all.sql
MariaDB [(none)]> start slave;

2、当master服务器宕机，提升一个slave成为新的master（写出操作步骤）

#首先在从节点中找到复制信息最多的从节点
[root@centos7 ~]#mysql -uroot -p123456
MariaDB [(none)]> show slave status\G

#修改从节点配置文件，取消只读模式，没有开启日志则要开启日志功能
[root@centos7 ~]#cat /etc/my.cnf.d/server.cnf
....
[mysqld]
server-id=17
log_bin=/data/mysql/logbin/mysql-bin

[root@centos7 ~]#mkdir -p /data/mysql/logbin
[root@centos7 ~]#chown -R mysql.mysql /data/mysql/logbin
[root@centos7 ~]#systemctl restart mariadb

#清楚旧的master信息
[root@centos7 ~]#mysql -uroot -p123456
MariaDB [(none)]> stop slave;
MariaDB [(none)]> reset slave all;

#完全备份，并发送到其他从节点
[root@centos7 ~]#mysqldump -uroot -p123456 -A -F -q --single-transaction --master-data=1 |gzip > backup_all.sql.gz

root@centos7 ~]#scp -r backup_all.sql.gz 10.0.0.27:

#解压文件，修改完全备份，重新配置主从
[root@centos7 ~]#gunzip backup_all.sql.gz
[root@centos7 ~]#cat backup_all.sql
....
CHANGE MASTER TO
MASTER_HOST='10.0.0.17', 
MASTER_USER='repluser', 
MASTER_PASSWORD='centos', 
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=245
....

[root@centos7 ~]#mysql -uroot -p123456
MariaDB [(none)]> set sql_log_bin=off;
MariaDB [(none)]> stop slave;
MariaDB [(none)]> reset slave all;
MariaDB [(none)]> source backup_all.sql
MariaDB [(none)]> set sql_log_bin=on;
MariaDB [(none)]> start slave;

3、通过 MHA 0.58 搭建一个数据库集群结构
第一步：环境准备
四台主机
10.0.0.7 CentOS7 MHA管理端
10.0.0.17 CentOS7 Master
10.0.0.27 CentOS7 Slave1
10.0.0.37 CentOS7 Slave2
四台主机都安装mysql5.7

第二步 软件包准备
准备
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm

第三步 配置管理节点

#在10.0.0.7上安装manager和node软件包
[root@mha-manager ~]#yum -y install mha4mysql*.rpm

#配置管理节点配置文件
[root@mha-manager ~]#mkdir /etc/mastermha/
[root@mha-manager ~]#vim /etc/mastermha/app1.cnf
[server default]
user=mhauser
password=centos
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root                                                                                                  
repl_user=repluser
repl_password=centos
ping_interval=1
master_binlog_dir=/data/mysql/
check_repl_delay=0
[server1]
hostname=10.0.0.17
candidate_master=1
[server2]
hostname=10.0.0.27
candidate_master=1
[server3]
hostname=10.0.0.37

第四步 实现主从

#实现master
[root@master ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@master ~]#mkdir /data/mysql/
[root@master ~]#chown mysql.mysql /data/mysql/
[root@master ~]#vim /etc/my.cnf
[mysqld]
server_id=17
log-bin=/data/mysql/mysql-bin
skip_name_resolve=1

[root@master ~]#systemctl restart mysqld
[root@master ~]#mysql -uroot -p123456
mysql>show master logs;

mysql>grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';

mysql>grant all on *.* to mhauser@'10.0.0.%' identified by '123456';

#实现从，分别在10.0.0.27和10.0.0.37
[root@master ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@master ~]#mkdir /data/mysql/
[root@master ~]#chown mysql.mysql /data/mysql/
[root@master ~]#vim /etc/my.cnf
[mysqld]
server_id=27  （10.0.0.37此处为37）
log-bin=/data/mysql/mysql-bin
read_only
relay_log_purge=0
skip_name_resolve=1

[root@master ~]#systemctl restart mysqld
[root@master ~]#mysql -uroot -p123456
mysql>CHANGE MASTER TO
MASTER_HOST='10.0.0.17',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_LOG_FILE='centos7-bin.000001',
MASTER_LOG_POS=154;
mysql>start slave；

第五步 实现key验证

[root@mha-manager ~]#ssh-keygen
[root@mha-manager ~]#ssh-copy-id 10.0.0.7
[root@mha-manager ~]#scp -r .ssh 10.0.0.17:/root/
[root@mha-manager ~]#scp -r .ssh 10.0.0.27:/root/
[root@mha-manager ~]#scp -r .ssh 10.0.0.37:/root/

第六步 检查Mha环境

[root@mha-manager ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf
[root@mha-manager ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf

4、实战案例：Percona XtraDB Cluster(PXC 5.7）
第一步 环境准备
三台服务器
pxc1:10.0.0.7
pxc2:10.0.0.17
pxc3:10.0.0.27
第二步配置yum源，安装Percona-XtraDB-Cluster-57

[root@pxc1 ~]#vim /etc/yum.repos.d/pxc.repo
[percona]
name=percona_repo
baseurl =https://mirrors.tuna.tsinghua.edu.cn/percona/release/7/RPMS/x86_64
enabled = 1
gpgcheck = 0
[root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.17:/etc/yum.repos.d
[root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.27:/etc/yum.repos.d

#在三个节点都安装好PXC 5.7 
[root@pxc1 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc2 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc3 ~]#yum install Percona-XtraDB-Cluster-57 -y

第三步 配置集群配置文件

[root@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
....
[mysqld]
server-id=7     #建议各个节点不同（17,27）
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin     
log_slave_updates
expire_logs_days=7
...

[root@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27  #三个节点的IP
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.7     #各个节点，指定自已的IP
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-1  #各个节点，指定自已节点名称（2,3）
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:123456"  

#启动第一节点
[root@pxc1 ~]#systemctl start mysql@bootstrap.service
[root@pxc1 ~]#grep "temporary password" /var/log/mysqld.log
[root@pxc1 ~]#mysql -uroot -p'Tt9t-uFvY)e6'
#修改密码
mysql> alter user 'root'@'localhost' identified by '123456';
#创建用户
mysql>create user  'sstuser'@'localhost' identified by '123456';
#权限
mysql> grant reload,lock tables,process,replication client on *.* to 'sstuser'@'localhost'；

#启动其他节点
[root@pxc2 ~]#systemctl start mysql
[root@pxc3 ~]#systemctl start mysql

5、通过 ansible 部署二进制 mysql 8
第一步：安装前准备

#把安装包放在/usr/local下
[root@centos8 ~]#ll /usr/local/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
#准备配置文件
[root@centos8 ~]#cat /etc/my.cnf
[mysqld]
server-id=8
log-bin
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

第二步：准备yml文件

[root@centos8 ~]#cat install_mysql8.yml 
---
# install mysql-8.0.19-linux-glibc2.12-x86_64
- hosts: 10.0.0.8
  remote_user: root

  tasks:
      - name: install packages
        yum: name=perl,libaio,ncurses-compat-libs
      - name: create mysql group
        group: name=mysql gid=306
      - name: create mysql user
        user: name=mysql uid=306 group=mysql shell=/sbin/nologin system=yes create_home=no 
      - name: copy tar to remote host and file mode 
        unarchive: src=/usr/local/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz dest=/usr/local/ owner=root group=root
      - name: create linkfile /usr/local/mysql 
        file: src=/usr/local/mysql-8.0.19-linux-glibc2.12-x86_64 dest=/usr/local/mysql state=link
      - name: config my.cnf      
        copy: src=/etc/my.cnf  dest=/etc/my.cnf
      - name: /usr/local/mysql/bin
        shell: ln -s /usr/local/mysql/bin/* /usr/bin/
      - name: data dir
        shell: mysqld --initialize --user=mysql --datadir=/data/mysql
      - name: mysql.server
        shell: cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
      - name: enable service
        shell: /etc/init.d/mysqld start;chkconfig --add mysqld;chkconfig mysqld on
      - name: password
        shell: PASSWORD=`awk '/temporary password/{print $NF}' /data/mysql/mysql.log`;mysqladmin -uroot -p${PASSWORD}  password 123456