1、mysql 安装（支持arm64和amd64）

mkdir /data/mysql/{etc,data,log} -p
chown 999  /data/mysql/log

cat >/data/mysql/etc/my.cnf << 'EOF'
[mysqld]
skip-host-cache
skip-name-resolve
skip_ssl
datadir=/var/lib/mysql
secure-file-priv=/var/lib/mysql-files
collation-server=utf8_bin
character-set-server=utf8
init_connect='set names utf8'
user=mysql
port=3306
max_connections=2000
group_concat_max_len=102400
max_allowed_packet=256M
#symbolic-links=0
log-bin=mysql-bin
server-id=1                 #主库server-id为1,备库server-id为2
binlog_format=row
relay-log-purge=0
#read_only=1                #备库配置
#log-slave-updates=true     #备库配置
#skip-slave-start=1         #备库配置  
binlog-ignore-db=mysql
replicate-ignore-db=mysql
####半自动同步+GTID
gtid-mode=on
enforce-gtid-consistency=1
plugin-load=rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_master_enabled = 1         
rpl_semi_sync_master_timeout = 2000      
rpl_semi_sync_slave_enabled = 1         
###################     Slow Log   ######################
slow_query_log = ON
# 开启慢查询日志
slow_query_log_file = /var/log/mysql/slow.log
# 慢查询日志存放路径
long_query_time = 10
# 超过10秒的查询，记录到慢查询日志，默认值10
log_queries_not_using_indexes = ON
# 没有使用索引的查询，记录到慢查询日志，可能引起慢查询日志快速增长
log_slow_admin_statements = ON
# 执行缓慢的管理语句，记录到慢查询日志
# 例如 ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.
###################     Error Log   ####################
log_error = /var/log/mysql/error.log
# 错误日志存放路径
log_error_verbosity = 2
# 全局动态变量，默认3，范围：1～3
# 表示错误日志记录的信息，1：只记录error信息；2：记录error和warnings信息；3：记录error、warnings和普通的notes信息
EOF

cat > /data/mysql/start.sh << 'EOF'
docker  run -itd \
-e MYSQL_ROOT_PASSWORD=Mysql123456 \
-v /data/mysql/data:/var/lib/mysql \
-v /data/mysql/log:/var/log/mysql \
-v /data/mysql/etc/my.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf \
-v /etc/localtime:/etc/localtime:ro \
--name mysql-server \
--network host \
--restart=always \
biarms/mysql:5.7.30
EOF

bash  /data/mysql/start.sh

2、主备配置

主节点：

#进入mysql-server容器中
docker exec -it mysql-server mysql -uroot -pMysql123456 
#设置主备同步的专用帐号
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%'  IDENTIFIED BY 'mysql_password';
flush privileges;
#查询主库状态
show master status;
mysql>  show master status
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |      757 |              | mysql            | 39f9df46-b88c-11eb-af08-000c29514619:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+

备库节点：

#进入mysql-server容器中
docker exec -it mysql-server mysql -uroot -pMysql123456
#停止主从同步
stop slave;
#设置主节点
CHANGE MASTER TO 
MASTER_HOST='192.168.11.194',  
MASTER_PORT=3306,
MASTER_USER='repl_user',
MASTER_PASSWORD='mysql_password',
MASTER_AUTO_POSITION=1;
#启动主从同步
start slave;
#显示主从同步状态
show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.194
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 757
               Relay_Log_File: centos7-relay-bin.000002
                Relay_Log_Pos: 970
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes      #状态正常
            Slave_SQL_Running: Yes      #状态正常
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 757
              Relay_Log_Space: 1179
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0          #主从同步时间差
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 39f9df46-b88c-11eb-af08-000c29514619
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 39f9df46-b88c-11eb-af08-000c29514619:1-3
            Executed_Gtid_Set: 39f9df46-b88c-11eb-af08-000c29514619:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 

#查询同步插件工作状态
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';      
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
| rpl_semi_sync_slave  | ACTIVE        |
+----------------------+---------------+

3、phpmyadmin安装（支持arm64和amd64）

mkdir /data/myadmin
cat > /data/myadmin/start.sh << 'EOF'
docker rm -f  myadmin
docker run -d \
--restart=always \
--name myadmin \
-e PMA_HOSTS=192.168.11.194 \
-p 28080:80 \
-v /etc/localtime:/etc/localtime \
phpmyadmin/phpmyadmin:latest
EOF

bash /data/myadmin/start.sh

4、mysql备份与恢复


mkdir -p /data/mysql_backup/auto_backup

cat > /data/mysql_backup/backup.sh << 'EOF'
#!/bin/bash
#########mysql backup####################
db_user="root" 
db_passwd="Mysql123456" 
db_host="192.168.11.192" 
db_port="3306"
MYSQL="docker exec -i mysql-server mysql" 
MYSQLDUMP="docker exec -i mysql-server mysqldump" 

##########code & res backup####################
backup_dir="/data/mysql_backup/auto_backup"
time="$(date +"%d-%m-%Y")" 
MKDIR="/bin/mkdir" 
RM="/bin/rm" 
MV="/bin/mv" 
GZIP="/bin/gzip" 

# the directory for story the newest backup 
test ! -d "$backup_dir/backup.0/" && $MKDIR -p "$backup_dir/backup.0/" 

# check the directory for store backup is writeable 
test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0 

backup()
{
# get all databases 
databases=(gdkf
message
nsfx) 

for db in ${databases[*]}
  do
    #备份指定库
    $MYSQLDUMP  -u$db_user -h$db_host -p$db_passwd -P$db_port $db --single-transaction --set-gtid-purged=OFF | $GZIP -9 > "$backup_dir/backup.0/$time.$db.gz"   
    #备份存储过程、触发器、函数等
    $MYSQLDUMP -u$db_user -h$db_host -p$db_passwd -P$db_port -n -t -d -R $db --single-transaction | $GZIP -9 > "$backup_dir/backup.0/$time.proc_$db.gz"    
done     
}

backup_all()
{
 $MYSQLDUMP  -u$db_user -h$db_host -p$db_passwd -P$db_port --all-databases --set-gtid-purged=OFF | $GZIP -9 > "$backup_dir/backup.0/mysql_$time.gz"   
}

#备份所有数据库
backup_all
####滚动清理历史备份
test -d "$backup_dir/backup.5/" && $RM -rf "$backup_dir/backup.5" 
for int in 4 3 2 1 0 
do 
if(test -d "$backup_dir"/backup."$int") 
then 
next_int=`expr $int + 1` 
$MV "$backup_dir"/backup."$int" "$backup_dir"/backup."$next_int" 
fi 
done 

exit 0;
EOF
  • 恢复
cat > /data/mysql_backup/restore.sh << 'EOF'
#!/bin/bash
#########mysql backup####################
db_user="root" 
db_passwd="Mysql123456" 
db_host="192.168.11.192" 
db_port="3306"
MYSQL="docker exec -i mysql-server mysql" 
MYSQLDUMP="docker exec -i mysql-server mysqldump" 

##########code & res backup####################
backup_dir="/data/mysql_backup/auto_backup"
time="$(date +"%d-%m-%Y")" 
MKDIR="/bin/mkdir" 
RM="/bin/rm" 
MV="/bin/mv" 
GZIP="/bin/gzip" 

restore_all()
{
 zcat $backup_dir/backup.1/mysql*.gz|$MYSQL  -u$db_user -h$db_host -p$db_passwd -P$db_port
}

restore_all

exit 0;
EOF
Logo
向您推荐>>Eolink开发者社区

权威｜前沿｜技术｜干货｜国内首个API全生命周期开发者社区

更多推荐

ELK实现containerd的容器日志采集展示【基于logging的全栈监测】

企业级ELK Stack构建介绍

avatar 云原生

深入理解 Mocha 测试框架：从零实现一个 Mocha

前言什么是自动化测试自动化测试在很多团队中都是Devops环节中很难执行起来的一个环节，主要原因在于测试代码的编写工作很难抽象，99%的场景都需要和业务强绑定，而且写测试代码的编写工作量往往比编写实际业务代码的工作量更多。在一些很多业务场景中投入产出比很低，适合写自动化测试的应该是那些中长期业务以及一些诸如组件一样的基础库。自动化测试是个比较大的概念，其中分类也比较多，比如单元测试，端对端测试，集

avatar 云原生

(20200916 Solved)docker-compose up创建容器自动退出

问题描述如题，创建容器后自动退出了。并且docker start container无效解决方案原因是缺失了控制终端的配置，需要在docker-compose.yml中增加tty:true ，有时候这样也不行，需要再增加一个command:/bin/bash，命令不一定是这个，需要是一个不会退出的命令，然后用-d后台启动容器。Referencesdocker-compose启动容器后自动退出...

avatar 云原生