mysql--备份/主从同步/安装

1、编写脚本，支持让用户自主选择，使用mysqldump还是xtraback全量备份。

#!/bin/bash
date=`date '+%F'`
sock="-S /data/mysql/mysql3306/socket/mysql.sock"
dbdir="/backup/db"

read -p "pleas input you db username :" user
read -p "pleas input you db password :" passwd

#mysqldump方法备份
sqldump(){
  while :;do
     read -p "Are you bakup all databases: " yn
     if [[ "$yn" =~ ^[yY]([Ee][Ss])?$ ]];then
     #备份所有库
          mysqldump $sock  -u${user} -p${passwd} -A -F --single-transaction --master-data=2|gzip > ${dbdir}/all-${date}.sql.gz
          echo "database bakup in ${dbdir}/all-${date}.sql.gz"
          exit 0
     elif [[ "$yn" =~ ^[Nn][Oo]?$ ]];then
          mysql $sock -u${user} -p${passwd} -e 'show databases'
          read -p "chose you bakup databases :" db
          #备份部分库
          mysqldump $sock -u${user} -p${passwd} -B ${db}|gzip > ${dbdir}/part-${date}.sql.gz
          echo "database bakup in ${dbdir}/part-${date}.sql.gz"
          exit 0
      else
          echo 'please input y or n'
          continue 1
      fi 
   done
}

#xtrabackup方法备份
xtrabak(){
      read -p "input you backup dir(default /backup/db): " xdbdir
      [ -z $xdbdir ] && xdbdir=$dbdir  
      [ -d $xdbdir ] || mkdir -p $dbdir
      mariabackup $sock --backup --target-dir=${xdbdir} --user=$user
}

main(){
      read -p "please chose you backup 1:mysqldump,2:xtrabackup: " num
      if [ "$num" -eq 1 ];then
         sqldump
      elif [ $num -eq 2 ];then
         xtrabak
      else
         echo "please input 1 or 2"
      fi
}

main

2、配置Mysql主从同步

1 主服务器
vim /etc/my.cnf server-id=1 log-bin=/data/logbin/mysql

mysql> grant replication slave on . to repluser@‘192.168.36.%’
identified by ‘centos’;

mysqldump -A --single-transaction --master-data=1 -F > /data/all.sql
scp /data/all.sql 将来的从服务器上:/data

2 从服务器
vim /etc/my.cnf server-id=2 read-only

vim /data/all.sql CHANGE MASTER TO MASTER_HOST=‘192.168.37.7’,
MASTER_USER=‘repluser’, MASTER_PASSWORD=‘centos’, MASTER_PORT=3306,
MASTER_LOG_FILE=‘mariadb-bin.000002’, MASTER_LOG_POS=245;

mysql < /data/all.sql mysql> start slave;

insert teachers (name,age) values(‘wang’,30);

slave-skip-errors=1062

1 主服务器

vim /etc/my.cnf
server-id=1
log-bin=/data/logbin/mysql

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

mysqldump -A --single-transaction --master-data=1  -F > /data/all.sql 
scp /data/all.sql 将来的从服务器上:/data

2 从服务器

vim /etc/my.cnf
server-id=2
read-only


vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='192.168.37.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,                                                                                                       MASTER_LOG_FILE='mariadb-bin.000002', 
MASTER_LOG_POS=245;

mysql < /data/all.sql
mysql> start slave;

insert teachers (name,age) values('wang',30);

3、使用MHA实现Mysql高可用。

• 在管理节点上安装两个包
  mha4mysql-manager
  mha4mysql-node
• 在被管理节点安装
  mha4mysql-node
• 在管理节点建立配置文件
  vim /etc/mastermha/app1.cnf
  [server default] user=mhauser password=magedu 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=magedu ping_interval=1
  [server1] hostname=192.168.8.17 candidate_master=1 [server2] hostname=192.168.8.27 candidate_master=1 [server3] hostname=192.168.8.37
• 实现Master
  vim /etc/my.cnf
  [mysqld]
  log-bin server_id=1
  skip_name_resolve=1
  mysql>show master logs mysql>grant replication slave on . to repluser@‘192.168.8.%’ identified by ‘magedu’; mysql>grant all on . to mhauser@‘192.168.8.%’identified by‘magedu’;
• 实现slave
  vim /etc/my.cnf
  [mysqld]
  server_id=2 不同节点此值各不相同
  log-bin read_only relay_log_purge=0 skip_name_resolve=1
  mysql>CHANGE MASTER TO MASTER_HOST=‘MASTER_IP’, MASTER_USER=‘repluser’, MASTER_PASSWORD=‘magedu’, MASTER_LOG_FILE=‘mariadb-bin.000001’, MASTER_LOG_POS=245;
• 在所有节点实现相互之间ssh key验证
• Mha验证和启动
  masterha_check_ssh --conf=/etc/mastermha/app1.cnf masterha_check_repl --conf=/etc/mastermha/app1.cnf masterha_manager --conf=/etc/mastermha/app1.cnf
• 排错日志： /data/mastermha/app1/manager.log

1、 导入hellodb.sql生成数据库

(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

select name,age from students where age > 25 and gender = ‘男’；

(2) 以ClassID为分组依据，显示每组的平均年龄

select classid,avg(age) from students group by classid;

(3) 显示第2题中平均年龄大于30的分组及平均年龄

select classid,avg(age) as age from students group by classid having age > 30;

(4) 显示以L开头的名字的同学的信息

select * from students where name like ‘L%’；

2、数据库授权magedu用户，允许192.168.1.0/24网段可以连接mysql

create user magedu@‘192.168.1.%’ identified by ‘123456’;
select user,host,password from mysql.user where user=‘magedu’;
grant all on . to magedu@‘192.168.0.%’ identified by ‘123456’;
show grants for magedu@‘192.168.0.%’;
update user set host=‘192.168.0.%’ where user=‘magedu’;
flush privileges;
select user,host,password from user where user=‘magedu’;

3、总结mysql常见的存储引擎以及特点。

• MyISAM引擎特点
  不支持事务
  表级锁定
  读写相互阻塞，写入不能读，读时不能写
  只缓存索引
  不支持外键约束
  不支持聚簇索引
  读取数据较快，占用资源较少
  不支持MVCC（多版本并发控制机制）高并发
  崩溃恢复性较差
  MySQL5.5.5前默认的数据库引擎
• InnoDB引擎特点
  行级锁
  支持事务，适合处理大量短期事务
  读写阻塞与事务隔离级别相关
  可缓存数据和索引
  支持聚簇索引
  崩溃恢复性更好
  支持MVCC高并发
  从MySQL5.5后支持全文索引
  从MySQL5.5.5开始为默认的数据库引擎
• 其它存储引擎
• Performance_Schema：Performance_Schema数据库使用
• Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似 数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎
• MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表 进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库
• Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持 SELECT和INSERT操作；支持行级锁和专用缓存区
• Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一 个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而 后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务 器创建一个逻辑数据库。非常适合分布式或数据集市环境

mysql二进制安装

# tar xvf mysql-5.7.26-el7-x86_64.tar.gz  -C /usr/local/
# useradd -r -s /bin/false mysql 
# ln -s /usr/local/mysql-5.7.26-el7-x86_64/ /usr/local/mysql
# mkdir /data/mysql 
# chown mysql.mysql /data/mysql /usr/local/mysql

# cd /usr/local/mysql
# mysqld --initialize --user=mysql --datadir=/data/mysql 
# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
# cp /usr/local/mysql/support-files/mysql-huge.cnf  /etc/my.cnf 
vim /etc/my.cnf
[mysqld]
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


# cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld 
chkconfig --add mysqld
chkconfig --list

# service mysqld start