1 Star 0 Fork 0

wizardhan / mysql_dockerfile

加入 Gitee
与超过 1200万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
该仓库未声明开源许可证文件(LICENSE),使用请关注具体项目描述及其代码上游依赖。
克隆/下载
贡献代码
同步代码
取消
提示: 由于 Git 不支持空文件夾,创建文件夹后会生成空的 .keep 文件
Loading...
README

#mysql_dockerfile

安装git: yum -y install git git --version git clone https://git.oschina.net/wizardhan/mysql_dockerfile.git

安装:yum -y install docker docker -v 启动:systemctl start docker.service 开机自启动:systemctl enable docker.service 查看已启动的服务列表:systemctl list-unit-files|grep enabled

pip 是一个Python包管理工具 查看pip是否安装: pip --version 更新版本:pip install --upgrade pip 安装python-pip: yum -y install python-pip

安装docker-compose: pip install -U docker-compose docker-compose -v

mysql5.7默认配置文件:/etc/mysql/mysql.conf.d/mysqld.cnf

mkdir -p /log/mysql/ /data/mysql && chown -R 999:999 /log/mysql/ /data/mysql

安装mysql client: yum -y install mariadb mysql -h127.0.0.1 -uroot -p

查看慢查询状态:show variables like '%query%'; 查看二进制日志:show variables like '%log_bin%';

启动:docker-compose up -d 重新生成镜像:docker-compose up --build -d

grant select,insert,update,delete on DIA.* to 'dia'@'10.10.67.190' identified by 'dia_!@#'; grant select on DIA.* to 'yitian'@'%' identified by 'yitian_!@#'; flush privileges;

show variables like '%per_table%'; show variables like '%query_cache%'; show variables like '%innodb_buffer_pool%';

docker exec -it db3 /bin/sh docker cp 4a8a3148f8a1:/etc/mysql/mysql.conf.d/mysqld.cnf ./

Mysql主从复制:

CREATE USER 'repl'@'IP段' identified by 'PassWOrd'; 基于日志点的复制配置步骤: 优点: 是Mysql最早支持的复制技术,Bug相对较少 对SQL查询没有任何限制 故障处理比较容易 缺点: 故障转移时重新获取新主的日志点信息比较困难

初始化从服务器数据: mysqldump --master-data=2 --single-transaction --master-data 这个参数在建立slave数据库的时候会用到,当这个参数的值为1的时候,mysqldump出来的文件包括CHANGE MASTER TO这个语句,CHANGE MASTER TO后面紧接着就是file和position的记录,file和position记录的位置就是slave从master端复制文件的起始位置。默认情况下这个值是1 当这个值是2的时候,chang master to也是会写到dump文件里面去的,但是不会有上面那个作用了 --single-transaction 1 只适用于innodb; 2 需要repeatable read模式开启一个事务 3 执行期间不阻碍DML和DDL,但是不要人工执行alter/create/drop/rename/truncate table; 4 不能与lock-tables共用,后者执行的LOCK TABLES会隐式提交所有pending事务 执行流程 (1)SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ (2)START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ (3)UNLOCK TABLES 如果mysqldump只指定single-transaction,期间不会加锁也不会阻止任何的事务;

xtrabackup --slave-info

配置从服务器Slave: change master to master_host='master_host_ip', master_user='repl', master_password='PassWOrd', master_log_file='mysql_log_file_name', master_log_pos=4; start slave; //启动从服务器复制功能

主库: CREATE USER 'repl'@'10.%.%.%' identified by '123456'; grant replication slave on . to 'repl'@'10.%.%.%'; mysqldump --master-data=2 --single-transaction --triggers --routines --all-databases -uroot -p -h127.0.0.1 >> all.sql scp all.sql root@123.56.165.125:/root show processlist;

从库: haproxy健康检查账号:CREATE USER 'haproxy'@'192.168.1.%'; mysql -uroot -p -h127.0.0.1 < all.sql change master to master_host='10.170.226.228', master_user='repl', master_password='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=927; start slave; //启动从服务器复制功能 show slave status\G 注:Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO均属错误)。 show processlist;

基于GTID复制的优缺点: 基于日志点的复制,从服务器指定从哪个二进制日志的偏移量进行增量同步,如果指定错误会造成遗漏或重复,从而导致主从数据不一致。 基于GTID复制,从库告诉主库已执行事物的GTID值,主库会把未执行事物的GTID值发送到从库上,从库执行。同一事物只在指定的从库上执行一次,保证了主从数据一致。 优点: 可以很方便的进行故障转移 从库不会丢失主库上的任何修改

什么是GTID GTID即全局事物ID,其保证为每一个在主上提交的事物在复制集群中可以生成一个唯一的ID GTID=source_id:transaction_id source_id即server-uuid,mysql首次启动时自动生成。

主库: CREATE USER 'repl'@'10.%.%.%' identified by '123456'; grant replication slave on . to 'repl'@'10.%.%.%'; gtid_mode = on enforce-gtid-consistency enforce-gtid-consiste:强制事务一致性,保证事务的安全 不能使用: 1.create table 。。select 2.在事务中使用create temporary table 建立临时表,使用关联更新事务表和非事务表。

从库: change master to master_host='10.172.3.78', master_user='repl', master_password='123456', master_auto_position=1;

start slave;

初始化从服务器数据: 记录备份时最后的事务的GTID值 mysqldump --master-data=2 --single-transaction --triggers --routines --all-databases -uroot -p -h127.0.0.1 >> all.sql (Docker中需要到Docker容器中导出) scp all.sql root@123.56.165.125:/root mysql -uroot -p -h127.0.0.1 < all.sql

Mysql复制性能优化: 影响主从延迟的因素 主库写入二进制日志的时间 控制主库的事务大小,分割大事务到多个小事物 二进制日志传输时间

半同步复制 show plugins; 查看模块 加载插件

Master端的安装过程 INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; SET GLOBAL rpl_semi_sync_master_enabled = 1; SET GLOBAL rpl_semi_sync_master_timeout=100000; SHOW VARIABLES LIKE 'rpl_semi_sync_master_enabled'; 值为ON,表示开启;否则检查失败原因

Slave端的安装过程 INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; SET GLOBAL rpl_semi_sync_slave_enabled = 1; SHOW VARIABLES LIKE 'rpl_semi_sync_slave_enabled'; 值为ON,表示开启;否则失败检查原因

查看是否半同步: show status like '%semi%'; 主: Rpl_semi_sync_master_no_tx 从库未及时响应的事务数,如果这个值很大就有问题 Rpl_semi_sync_master_yes_tx Semi-sync模式下,成功的事务数

@@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. reset master;

MHA高可用: 生成ssh秘钥: 所有节点必须免认证包括主 ssh-keygen ssh-copy-id -i /root/.ssh/id_rsa root@10.170.226.228 ssh-copy-id -i /root/.ssh/id_rsa root@10.172.5.144 ssh-copy-id -i /root/.ssh/id_rsa root@10.172.3.78 数据节点安装MHA Node软件包: yum -y install perl-DBD-MySQL ncftp perl-DBI.x86_64 rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm 监控节点: yum -y install perl-Config-Tiny.noarch perl-Time-HiRes.x86_64 perl-Parallel-ForkManager perl-Log-Dispatch.noarch perl-DBD-MySQL ncftp rpm -ivh mha4mysql-manager-0.57-0.el7.noarch.rpm

chmod +x /usr/bin/master_ip_failover

mkdir -p /etc/mha mkdir -p /home/mysql_mha cd /etc/mha vi mysql_mha.cnf [server default] user=mha password=123456 manager_workdir=/home/mysql_mha manager_log=/home/mysql_mha/manager.log remote_workdir=/home/mysql_mha ssh_user=root repl_user=repl repl_password=123456 ping_interval=1 master_binlog_dir=/opt/log/mysql master_ip_failover_script=/usr/bin/master_ip_failover secondary_check_script=/usr/bin/masterha_secondary_check -s 10.172.3.78 -s 10.172.5.144 -s 10.170.226.228 [server1] hostname=10.170.226.228 candidate_master=1 [server2] hostname=10.172.3.78 candidate_master=1 [server3] hostname=10.172.5.144 no_master=1

主: grant all privileges on . to 'mha'@'10.%.%.%' identified by '123456'; mkdir -p /home/mysql_mha 配置虚拟IP: ip addr ifconfig eth0:1 192.168.1.100/24 ifconfig eth0:1 down

从: mkdir -p /home/mysql_mha

验证是否正确: masterha_check_ssh --conf=/etc/mha/mysql_mha.cnf masterha_check_repl --conf=/etc/mha/mysql_mha.cnf

启动mha_manager: nohup masterha_manager --conf=/etc/mha/mysql_mha.cnf &

故障转移切换后: 主变从


Mysql 监控 Innodb 阻塞状况 查看连接ID select connection_id();

select b.trx_mysql_thread_id as 被阻塞线程 ,b.trx_query as 被阻塞SQL ,c.trx_mysql_thread_id as 阻塞线程 ,c.trx_query as 阻塞SQL ,(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started)) as 阻塞时间 from information_schema.innodb_lock_waits a join information_schema.innodb_trx b on a.requesting_trx_id=b.trx_id join information_schema.innodb_trx c on a.blocking_trx_id=c.trx_id where (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started))> 10

备份: vi dia_db_backup.sh #!/bin/bash docker exec mysqldockerfile_mysql_1 mysqldump --master-data=2 --single-transaction -uroot -pdia_mysql_!@# -h127.0.0.1 DIA | pv -q -L 10M | gzip > /data/backup/dia_db_$(date -I).sql.gz

清楚七天前备份

find /data/backup/ -type f -name "*.gz" -mtime +5 | xargs rm -rf chmod +x dia_db_backup.sh crontab -e 59 23 * * * /bin/bash /data/backup/dia_db_backup.sh

导入:gunzip -fc /data/backup/dia_db.sql.gz | mysql -u -h -P DB

MySQL逻辑备份利器:mydumper xtrabackup:可以增量备份

Zabbix监控: 导入源: rpm -ivh http://repo.zabbix.com/zabbix/3.2/rhel/7/x86_64/zabbix-release-3.2-1.el7.noarch.rpm yum install -y zabbix-server-mysql zabbix-web-mysql yum install -y zabbix-agent

yum install -y mariadb-server mariadb systemctl enable mariadb.service systemctl start mariadb.service mysql_secure_installation mysql -uroot -h127.0.0.1 -p123456 create database zabbix character set utf8; grant all privileges on zabbix.* to 'zabbix'@'localhost' identified by 'zabbix'; gunzip create.sql.gz mysql -uroot -p123456 zabbix < create.sql

vi /etc/httpd/conf.d/zabbix.conf php_value date.timezone Asia/Shanghai systemctl restart httpd

vi /etc/zabbix/zabbix_server.conf DBHost=localhost DBName=zabbix DBUser=zabbix DBPassword=zabbix systemctl restart zabbix-server

使用浏览器打开 http://server_ip/zabbix,安装完成后默认的用户名是:admin,密码是zabbix

Mysql通过Gearman同步Redis: yum install -y unzip mariadb mariadb-server mariadb-devel gearmand php php-pecl-gearman libgearman libgearman-devel redis vi /etc/php.ini Asia/Shanghai

redis、gearman扩展安装: yum install -y php-pecl-gearman php-pecl-redis php --info | grep "gearman support"

mysql插件安装: wget https://launchpad.net/gearman-mysql-udf/trunk/0.6/+download/gearman-mysql-udf-0.6.tar.gz tar -xzf gearman-mysql-udf-0.6.tar.gz && cd gearman-mysql-udf-0.6 ./configure --with-mysql=/usr/bin/mysql_config --libdir=/usr/lib64/mysql/plugin make && make install

wget https://github.com/mysqludf/lib_mysqludf_json/archive/master.zip unzip master.zip && cd lib_mysqludf_json-master mv lib_mysqludf_json.so lib_mysqludf_json.so_bak gcc $(/usr/bin/mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c cp lib_mysqludf_json.so /usr/lib64/mysql/plugin

systemctl enable mariadb.service systemctl start mariadb.service mysql_secure_installation mysql -uroot -h127.0.0.1 -p123456

CREATE FUNCTION gman_do_background RETURNS STRING SONAME 'libgearman_mysql_udf.so'; CREATE FUNCTION gman_servers_set RETURNS STRING SONAME 'libgearman_mysql_udf.so'; CREATE FUNCTION gman_do RETURNS STRING SONAME "libgearman_mysql_udf.so"; CREATE FUNCTION gman_do_high RETURNS STRING SONAME "libgearman_mysql_udf.so"; CREATE FUNCTION gman_do_low RETURNS STRING SONAME "libgearman_mysql_udf.so"; CREATE FUNCTION gman_do_high_background RETURNS STRING SONAME "libgearman_mysql_udf.so"; CREATE FUNCTION gman_do_low_background RETURNS STRING SONAME "libgearman_mysql_udf.so"; CREATE AGGREGATE FUNCTION gman_sum RETURNS INTEGER SONAME "libgearman_mysql_udf.so";

CREATE FUNCTION json_array RETURNS STRING SONAME 'lib_mysqludf_json.so'; CREATE FUNCTION json_object RETURNS STRING SONAME 'lib_mysqludf_json.so';
CREATE FUNCTION json_members RETURNS STRING SONAME 'lib_mysqludf_json.so';
CREATE FUNCTION json_values RETURNS STRING SONAME 'lib_mysqludf_json.so';

select * from mysql.func;

Gearman启动: gearmand -d redis启动:redis-server

vi syncToRedis.php

addServer(); $worker->addFunction('syncToRedis', 'syncToRedis'); $redis = new Redis(); $redis->connect('127.0.0.1', 6379); echo("begin:\n"); while($worker->work()); function syncToRedis($job) { global $redis; $workString = $job->workload(); echo $workString; $work = json_decode($workString); $redis->set($work->id, $workString); } php syncToRedis.php 测试表:create table data( id int(4) not null primary key auto_increment, name char(20) not null); insert into data values(1, 'data1'); SELECT gman_servers_set('127.0.0.1:4730'); SELECT gman_do("syncToRedis", json_object(id as id, name as name)) AS test FROM data; 创建触发器: DELIMITER $$ CREATE TRIGGER datatoredis AFTER INSERT ON data FOR EACH ROW BEGIN SET @ret=gman_do_background('syncToRedis', json_object(NEW.id as `id`, NEW.name as `name`)); END$$ DELIMITER ; MySQL服务重启后会丢失Gearman服务器信息,解决方案: vi /var/lib/mysql/init_file.sql SELECT gman_servers_set('127.0.0.1:4730'); 然后在/etc/mysql/my.cnf的[mysqld]下加入 init-file=/var/lib/mysql/init_file.sql ######废弃########### mkdir -p /opt/log/mysql/ && mkdir -p /opt/data/mysql && touch /opt/log/mysql/error.log && touch /opt/log/mysql/mysql-slow.log && touch /opt/log/mysql/mysql.log chown -R systemd-bus-proxy:ssh_keys /opt/log/mysql/ /opt/data/mysql

空文件

简介

暂无描述 展开 收起
Docker
取消

发行版

暂无发行版

贡献者

全部

近期动态

加载更多
不能加载更多了
Docker
1
https://gitee.com/wizardhan/mysql_dockerfile.git
git@gitee.com:wizardhan/mysql_dockerfile.git
wizardhan
mysql_dockerfile
mysql_dockerfile
master

搜索帮助