Mysql install

• 安装 mysql环境(阿里云centos7.6)

1. YUM安装

    rpm -Uvh  http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm  
    ls /etc/yum.repos.d/
    yum makecache
    yum -y install mysql-community-server-5.7.24 
    ps aux | grep mysql
    systemctl start mysqld
    systemctl enable mysqld
    ps aux | grep mysql 
    grep "password" /var/log/mysqld.log
    mysqladmin -uroot  -p  password 'qB23s34321!c227Mqw'
    create user grafana@'172.18.58.%' identified by 'BsEs!c27~sscMVM8';
    grant all on 库名.* to grafana@'172.18.58.%';
    revoke select on 库名.* from  grafana@'%';
    GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.18.214.185';
    update mysql.user set host='172.18.214.%'   where user='grafana' 

2. docker安装

1 备注： 使用的是daocloud镜像
2 安装docker

# step 1: 安装必要的一些系统工具
sudo yum install -y yum-utils device-mapper-persistent-data lvm2
# Step 2: 添加软件源信息
sudo yum-config-manager --add-repo http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo
# Step 3: 更新并安装Docker-CE
sudo yum makecache fast
sudo yum -y install docker-ce
# Step 4: 开启Docker服务
sudo service docker start

# 注意：
# 官方软件源默认启用了最新的软件，您可以通过编辑软件源的方式获取各个版本的软件包。例如官方并没有将测试版本的软件源置为可用，您可以通过以下方式开启。同理可以开启各种测试版本等。
# vim /etc/yum.repos.d/docker-ee.repo
#   将[docker-ce-test]下方的enabled=0修改为enabled=1
#
# 安装指定版本的Docker-CE:
# Step 1: 查找Docker-CE的版本:
# yum list docker-ce.x86_64 --showduplicates | sort -r
#   Loading mirror speeds from cached hostfile
#   Loaded plugins: branch, fastestmirror, langpacks
#   docker-ce.x86_64            17.03.1.ce-1.el7.centos            docker-ce-stable
#   docker-ce.x86_64            17.03.1.ce-1.el7.centos            @docker-ce-stable
#   docker-ce.x86_64            17.03.0.ce-1.el7.centos            docker-ce-stable
#   Available Packages
# Step2: 安装指定版本的Docker-CE: (VERSION例如上面的17.03.0.ce.1-1.el7.centos)
# sudo yum -y install docker-ce-[VERSION]

#step5
**配置daocloud**
cat /etc/docker/daemon.json 
{
 "registry-mirrors": ["http://f13621db2.m.daocloud.io"]
}
**没有账号自行注册daocloud**

docker run -it -d  --name mysql -p 3306:3306 -v /home/mydata/:/var/lib/mysql  -v /home/myconf/:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD='qBs!c27Mqw'  daocloud.io/library/mysql:5.7.24

• 自建服务器与阿里云搭建主从同步

使用mysqldump在主服务器上锁表备份
mysql> flush tables with read lock;
[root@master ~]# mysqldump --all-databases > all.sql
[root@master ~]# mysql -e 'show master status'
mysql> unlock tables; //解锁表
- - - - - - - - - - - - - - - - 
   一、同步rds数据到slave.
1. 停掉所有服务。HX1: sh docker_stop_all.sh   HX2: docker stop backendinst3
2. 登录主库服务器，
3. 执行锁表：FLUSH TABLES WITH READ LOCK;
4. 查看主库状态 show master status; 记录下 master_log_file和master_log_pos
5. 导出mysql数据库; 在slave上执行  mysqldump -h rm-wz946af9t6w5c9f9l.mysql.rds.aliyuncs.com -P 3306 -u root -p --set-gtid-purged=OFF hx>mysql_backend111.sql
6. 登录从服务器，导入数据；mysql -uroot -p hx < mysql_backend111.sql
7. 回到主库执行：UNLOCK TABLES;
8. 进入从服务器
    1.   stop  slave;
    2.  reset slave; 
    3. change master to master_host='rm-wz946af9t6w5c9f9l.mysql.rds.aliyuncs.com', master_user='slave', master_password='sFawWsd5c%Pp9sf9d',master_log_file='mysql-bin.00000508', master_log_pos=1901sd94447; 
    4.  start slave;
    5.  show slave status\G

• 查看进程

批量Kill  查询进程
select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;

查询执行的进程按时间排序
select * from information_schema.processlist where Command != 'Sleep' order by Time desc;

   create database db;
   use db;
   show tables;
    create user Jump@'%' identified by 'BEMVM8';
create user grafana@'localhost' identified by 'BE!c27~cMVM8';

create user xxb@'172.18.214.%' identified by 'BE!c27~cMVM8';
grant select on hx.* to xxb@'172.18.214.%';


grant select on hx.* to grafana@'localhost';
grant select on hx.* to grafana@'%';

grant select on hx.* to 'grafana'@'172.18.214.172';

revoke select on hx.* from  grafana@'%';
revoke select on hx.* from  grafana@'%';

select  host,user  from mysql.user;

delete from  mysql.user where  User='grafanagrafana' and host='%';


update mysql.user set host='172.18.214.%'   where user='grafana' 

update mysql.user set host='172.18.121.172'   where user='xxb'