MYSQL纪录片2

上图是MYSQL复制方式的架构图。MYSQL分别有异步复制、同步复制、组复制三大类型。

异步复制

1. 逻辑上：MYSQL默认的复制就是异步的，主库在执行客户端提交的事务后会立即将结果返回给客户端，实际上并不关系从库是否已经接收并处理。此时，如果主库宕机了，此时主库上已经提交的事务可能并没有传到从库上，若强行将就从库提升为主库，可能导致新的主库上的数据不完整。
2. 技术上：主库将事务binlog事件写入到binlog文件中，此时主库只会通知一下dump线程发送这些新的binlog，然后主库就会继续处理提交操作，而此时不会保证这些binlog传到任何一个从库节点上。

一主多从搭建

# 注意：本次实验使用的MYSQL版本为mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
# 本次实验所有MYSQL实例都在1台服务器上运行，使用端口区分实例。
# 主库：3306
# 从库：3307、3308、3309

# 上传MYSQL压缩包，解压
tar xzf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz -C /usr/local/mysql

# 进入安装目录
cd /usr/local/mysql

# 创建目录文件
mkdir -p /usr/local/mysql/data/{
   3306,3307,3308,3309}
# 初始化数据库：
scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/3306 --user=mysql
scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/3307 --user=mysql
scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/3308 --user=mysql
scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/3309 --user=mysql

# 编写主库配置文件
cat > /usr/local/mysql/data/330$i/my.cnf <<-EOF
[client]
port = 3306
socket = /usr/local/mysql/data/3307/mysql.sock
default-character-set = utf8
[mysqld]
port = 3306
socket = /usr/local/mysql/data/3306/mysql.sock
datadir = /usr/local/mysql/data/3306
log-error = /usr/local/mysql/data/3306/error.log
pid-file = /usr/local/mysql/data/3306/mysql.pid
character-set-server = utf8
lower_case_table_names = 1
autocommit = 1
# 主库必须开启bin-log日志
log-bin = mysql-bin
server-id = 3306
EOF > /usr/local/mysql/data/3306/my.cnf

# 编写从库配置文件
for i in `seq 7 9`
do
cat > /usr/local/mysql/data/330$i/my.cnf << -EOF
[client]
port = 330$i
socket = /usr/local/mysql/data/330$i/mysql.sock
default-character-set = utf8
[mysqld]
port = 330$i
socket = /usr/local/mysql/data/330$i/mysql.sock
datadir = /usr/local/mysql/data/330$i
log-error = /usr/local/mysql/data/330$i/error.log
pid-file = /usr/local/mysql/data/330$i/mysql.pid
character-set-server = utf8
lower_case_table_names = 1
autocommit = 1
EOF
done

# 启动数据库
bin/mysqld_safe --defaults-file=/usr/local/mysql/data/3306/my.cnf &
bin/mysqld_safe --defaults-file=/usr/local/mysql/data/3307/my.cnf &
bin/mysqld_safe --defaults-file=/usr/local/mysql/data/3308/my.cnf &
bin/mysqld_safe --defaults-file=/usr/local/mysql/data/3309/my.cnf &

# 所有数据库设置root密码
bin/mysql -uroot -p -P<端口>
SQL> set password = password('root');

# 主库配置同步账户
SQL> grant replication slave on *.* to 'repl'@'%' identified by 'repl';
SQL> reset master;
# 获取主库bin-log日志和pos位置
SQL> show master status\G;

# 所有备库配置同步主库配置
for i in `seq 7 9`
do
bin/mysql -uroot -proot -P330$i -e "change master to master_host='<主库ip>', master_port=3307,master_user='repl', master_password='repl', master_log_file='mysql-bin.000001',master_log_pos=120;"
done

# 所有备库开启同步
for i in `seq 7 9`
do
bin/mysql -uroot -proot -P330$i -e "start slave;"
done

双主双从搭建

# 3306和3307为双主，3308、3309为双从
# 3308同步3307的数据，3309同步3307的数据
# 上传MYSQL压缩包，解压
tar xzf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz -C /usr/local/mysql

# 进入安装目录
cd /usr/local/mysql

# 创建目录文件
mkdir -p /usr/local/mysql/data/{
   3306,3307,3308,3309}
# 初始化数据库：
scripts/mysql_install_db --basedir=/usr/local/mysql/