mysql主从

最新推荐文章于 2024-09-12 01:15:37 发布

qq_34145309

最新推荐文章于 2024-09-12 01:15:37 发布

阅读量147

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_34145309/article/details/100549996

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

生成配置文件

cat <<EOF | tee /etc/mysql_330{6,7}.cnf
[server]
port=3306
datadir=/usr/local/mysql/data_3306
socket=/usr/local/mysql/data_3306/mysql.sock
pid_file=/usr/local/mysql/data_3306/mysql.pid
explicit_defaults_for_timestamp=on
log_timestamps=SYSTEM

[client]
port=3306
socket=/usr/local/mysql/data_3306/mysql.sock

[mysqld]

[mysqld_safe]
mysqld-safe-log-timestamps=SYSTEM
EOF

sed -i 's/3306/3307/g' /etc/mysql_3307.cnf

生成启动脚本

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql_3306
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql_3307
sed -i 's/Provides:.*/Provides: mysql_3306/' /etc/init.d/mysql_3306
sed -i 's/Provides:.*/Provides: mysql_3307/' /etc/init.d/mysql_3307
sed -i 's/Default-Start:.*$/Default-Start: 2 3 5/' /etc/init.d/mysql_3306
sed -i 's/Default-Start:.*$/Default-Start: 2 3 5/' /etc/init.d/mysql_3307
sed -i 's/$$bindir\/mysqld_safe$ --datadir="$datadir" --pid-file="$mysqld_pid_file_path"/\1 --defaults-file=\/etc\/mysql_3306\.cnf/' /etc/init.d/mysql_3306
sed -i 's/$$bindir\/mysqld_safe$ --datadir="$datadir" --pid-file="$mysqld_pid_file_path"/\1 --defaults-file=\/etc\/mysql_3307\.cnf/' /etc/init.d/mysql_3307
sed -i 's/^mysqld_pid_file_path=.*/mysqld_pid_file_path=\/usr\/local\/mysql\/data_3306\/mysql.pid/' /etc/init.d/mysql_3306
sed -i 's/^mysqld_pid_file_path=.*/mysqld_pid_file_path=\/usr\/local\/mysql\/data_3307\/mysql.pid/' /etc/init.d/mysql_3307
chkconfig --add mysql_330{6,7}

添加环境变量
cat > /etc/profile.local <<EOF
export PATH=$PATH:/usr/local/mysql/bin
export MANPATH=$MANPATH:/usr/local/mysql/man
EOF

创建数据目录
mkdir -p /usr/local/mysql/data_330{6,7}
chmod 600 /usr/local/mysql/data_330{6,7}
chown mysql:mysql /usr/local/mysql/data_330{6,7}

初始化数据库
/usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql_3306.cnf --initialize-insecure --user=mysql
/usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql_3307.cnf --initialize-insecure --user=mysql
/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data_3306
/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data_3307
chown mysql:mysql /usr/local/mysql/data_3306/*.pem
chown mysql:mysql /usr/local/mysql/data_3307/*.pem

启动数据库
service mysql_3306 start
service mysql_3307 start

建立主从用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'mysql';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

导出主机数据库数据 --single-transaction针对innodb表可以避免锁库 --master-data=2使得导出的sql中change master语句被注释掉
mysqldump -uroot -pmysql --socket=/usr/loca/mysql/data_3306/mysql.sock --all-databases --single-transaction --master-data=2 >dbdump.sql

在从机配置主从连接
CHANGE MASTER TO
MASTER_HOST='localhost',
MASTER_USER='repl',
MASTER_PASSWORD='mysql',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=923;