3. 主从复制简单搭建(基于位点)

标签：主从复制 name source SOURCE MASTER log mysql 位点 搭建

1. 配置参数：

• server_id ：主备server都要配置，数字要不一样，取值范围：1 ~ 2^32    
• log_bin  : 要开启    
• log_slave_updates ：要开启

2. 创建用户　

mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

3. Obtaining the Replication Source Binary Log Coordinates

mysql> FLUSH TABLES WITH READ LOCK;   ## 设置一个只读锁，然后找到master节点的file 和position

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

 

4. Mysqldump方式

　　4.1  Creating a Data Snapshot Using mysqldump(用mysqldump 备份一个快照)　　

   # 这种访问不用停止源库，只需要用flush tables with read lock 让数据库处于只读状态，然后再用逻辑备份mysqldump进行备份  
　　$> mysqldump --all-databases --master-data > dbdump.db   # 这里是一个是一个最简单的备份，具体的参数项 --single-transaction  -R  --master-data 这些参数可以根据实际情况添加

　　4.2. On the source, released the read lock

   mysql> UNLOCK TABLES;

　　4.3 . Setting Up Replication with New Source and Replicas

   $> mysql -h source < fulldb.dump  ## 在备库上执行

     4.4 Setting the Source Configuration on the Replica (在备库上执行)

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='source_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

Or from MySQL 8.0.23:
mysql> CHANGE REPLICATION SOURCE TO
    ->     SOURCE_HOST='source_host_name',
    ->     SOURCE_USER='replication_user_name',
    ->     SOURCE_PASSWORD='replication_password',
    ->     SOURCE_LOG_FILE='recorded_log_file_name',
    ->     SOURCE_LOG_POS=recorded_log_position;

5. Creating a Data Snapshot Using Raw Data Files(使用拷贝文件的方式)

　5.1  步骤：　　

　# 这种拷贝数据文件的方法，首先要flush table with read lock,然后还要停库。再拷贝数据文件　
　　1. Acquire a read lock and get the source's status
　　2. In a separate session, shut down the source server:
　　　　$> mysqladmin shutdown
　　3.Make a copy of the MySQL data files
　　　　$> tar cf /tmp/db.tar ./data
　　　　$> zip -r /tmp/db.zip ./data
　　　　$> rsync --recursive ./data /tmp/dbdata
　　4.Restart the source server.

   5.2 On the source, released the read lock

  mysql> UNLOCK TABLES;

   5.3  If you created a snapshot using the raw data files, extract the data files into your replica's data directory. For example:

 $> tar xvf dbdump.tar 

   5.4 Setting the Source Configuration on the Replica

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='source_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

Or from MySQL 8.0.23:
mysql> CHANGE REPLICATION SOURCE TO
    ->     SOURCE_HOST='source_host_name',
    ->     SOURCE_USER='replication_user_name',
    ->     SOURCE_PASSWORD='replication_password',
    ->     SOURCE_LOG_FILE='recorded_log_file_name',
    ->     SOURCE_LOG_POS=recorded_log_position;

参考 :　 https://dev.mysql.com/doc/refman/8.0/en/binlog-replication-configuration-overview.html　　 

标签：主从复制,name,source,SOURCE,MASTER,log,mysql,位点,搭建
From： https://www.cnblogs.com/zmc60/p/17041653.html