1. 添加MySQL节点到install.conf文件中
[bkce-basic] 172.27.16.44 kafka(config),zk(config),es,consul,bkdata(databus),nginx,influxdb,paas,cmdb,job,gse,license,redis,appo 172.27.16.38 kafka(config),zk(config),es,consul,bkdata(dataapi),mongodb,mysql,beanstalk,fta,rabbitmq 172.27.16.88 kafka(config),zk(config),es,consul,bkdata(monitor),appt,mysql
2. 利用bkcec安装mysql
./bkcec sync common ./bkcec sync mysql ./bkcec install mysql ./bkcec initdata mysql ./bkcec status mysql
3. 修改数据库密码
vim /etc/my.cnf skip-grant-tables # 加入到mysqld中 cd /data/bkce/service/mysql/bin ./mysql.sh restart #重启数据库 ./mysql -uroot -p -h 172.27.16.88 mysql> use mysql; mysql> update mysql.user set authentication_string=PASSWORD('password') where user='root'; flush privileges; # 重置root密码为password 删除skip-grant-tables重启即可登录
注意：需要检查/etc/my.cnf中是否开启bin-log，如下截图
4. 备份主数据库并同步从数据库
./mysqldump -uroot -p -h 172.27.16.38 --set-gtid-purged=OFF --databases bk_bkdata_api bk_fta_solutions bk_log_search bk_monitor bk_nodeman bk_sops bkdata_monitor_alert bksuite_common job jobLog open_paas > /mysqldump.sql #备份 ./mysql -uroot -p -h 172.27.16.88 < /mysqldump.sql #导入
5. 创建用户并授予权限
CREATE USER 'test'@'172.27.16.88' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'test'@'172.27.16.88'; flush privileges;
6. 主从配置
主服务器操作：
查看master状态，记录二进制文件名(mysql-bin.000010)和位置(1116748)：
从服务器操作：
重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：
CHANGE MASTER TO MASTER_HOST='172.27.16.38', MASTER_USER='test', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=1116748;
启动slave同步进程：
mysql>start slave;
mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.27.16.38 Master_User: test Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 1881157 Relay_Log_File: VM-16-88-centos-relay-bin.000002 Relay_Log_Pos: 840574 Relay_Master_Log_File: mysql-bin.000010 Slave_IO_Running: Yes Slave_SQL_Running: Yes
JOB平台配置
作业平台配置MySQL主从 配置云参 参数类型 参数名 默认值 是否常量 字符 SLAVE_MYSQL_HOST slave IP地址 Y 字符 MASTER_MYSQL_HOST master IP地址 Y 字符 MASTER_POST_FILE None N 字符 MASTER_POSITION None N 字符 MYSQL_USER root N 脚本名称 bkcec_install_mysql.sh 中控机执行 #!/bin/bash ... # 省略默认脚本内容 cd /data/install/ && grep $SLAVE_MYSQL_HOST install.config|grep mysql||sed -r -i "s/($SLAVE_MYSQL_HOST.*)/1,mysql/g" install.config grep 'skip_grant_tables' /data/src/service/support-files/templates/#etc#my.cnf.tpl || sed -i '/[mysqld]/askip_grant_tables pid-file= /data/bkce/public/mysql/mysq.pid' /data/src/service/support-files/templates/#etc#my.cnf.tpl job_success $(./bkcec install mysql 1) config_mysql_cnf.sh slave 机器执行 #!/bin/bash ... # 修改server id sed -i -r 's/^(server-id.*=).*/1 2/g' /etc/my.cnf # 启动mysql job_success $(/data/bkce/service/mysql/bin/mysql.sh start) sync_data.sh 中控机执行 配置脚本参数，配置数据库密码 #!/bin/bash ... echo "开始同步数据..." mysqldump -u$MYSQL_USER -p$1 -h $MASTER_MYSQL_HOST --all-databases --master-data=2 |mysql -uroot -h $SLAVE_MYSQL_HOST create_mysql_user.sh 中控机执行 配置脚本参数，配置数据库密码 #!/bin/bash ... # 授权 mysql -u$MYSQL_USER -p$1 -h $MASTER_MYSQL_HOST -e "grant replication slave on *.* to 'replica'@'$SLAVE_MYSQL_HOST' identified by '123456';" mysql -u$MYSQL_USER -p$1 -h $MASTER_MYSQL_HOST -e 'flush privileges;' # job_success $(echo '开始授权') MASTER_POST_FILE=$(mysql -u$MYSQL_USER -p$1 -h $MASTER_MYSQL_HOST -e 'show master statusG'|sed -n '2,3p'|awk '{print $2}'|sed -n '1p') MASTER_POSITION=$(mysql -u$MYSQL_USER -p$1 -h $MASTER_MYSQL_HOST -e 'show master statusG'|sed -n '2,3p'|awk '{print $2}'|sed -n '2p') config_slave.sh 中控机执行 #!/bin/bash ... mysql -h $SLAVE_MYSQL_HOST -e "change master to master_host='$MASTER_MYSQL_HOST',master_user='replica',master_password='123456',master_log_file='$MASTER_POST_FILE',master_log_pos=$MASTER_POSITION;" job_success $(mysql -h $SLAVE_MYSQL_HOST -e "start slave;" ; mysql -h $SLAVE_MYSQL_HOST -e "show slave statusG")