向复制环境添加从机
可以在不停止主复制的情况下向现有复制配置添加另一个从复制。相反，可以通过创建一个现有从属服务器的副本来设置新的从属服务器，只不 过要用不同的服务器id值来配置新的从属服务器。
复制一个已存在的从节点。
1.关闭现有从服务器:
[mysql@localhost ~]$ mysqladmin -uroot -pxxzx7817600 shutdown mysqladmin: [Warning] Using a password on the command line interface can be insecure.
2.将数据目录从现有的从服务器复制到新的从服务器。您可以通过使用tar或WinZip创建存档，或者使用cp或rsync等工具执行直接复制来实现这 一点。确保还复制了日志文件和中继日志文件。
[root@localhost mysql]# rsync -avz * root@10.138.130.239:/mysqldata/mysql/ The authenticity of host '10.138.130.239 (10.138.130.239)' can't be established. ECDSA key fingerprint is 7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '10.138.130.239' (ECDSA) to the list of known hosts. root@10.138.130.239's password: sending incremental file list binlog.006119 ib_buffer_pool ib_logfile0 ibdata1 localhost-relay-bin.000004 localhost-relay-bin.000005 localhost-relay-bin.index master.info mysql.err relay-log.info ......... sent 86590 bytes received 262429 bytes 4748.56 bytes/sec total size is 22282098540 speedup is 63842.08
在添加新的复制从服务器时遇到的一个常见问题是，新的从服务器失败，并显示一系列警告和错误消息，如下所示:
071118 16:44:10 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=new_slave_hostname-relay-bin' to avoid this problem. 071118 16:44:10 [ERROR] Failed to open the relay log './old_slave_hostname-relay-bin.003525' (relay_log_pos 22940879) 071118 16:44:10 [ERROR] Could not find target log during relay log initialization 071118 16:44:10 [ERROR] Failed to initialize the master info structure
发生这种问题最可能的原因是启动mysql时没有指明–relay-log参数，在没有指定这个参数的情况下relay log会默认以主机名为前缀生成日志 文件。如果不指定–relay-log-index，relay log index文件也是如此。
要避免这个问题，把新从库的–relay-log的值设置为与原来的从库一致。如果原来的从库没有设置这个参数，那么把新从库的–relay-log参 数设置为existing_slave_hostname-relay-bin。如果不能这样设置，把原来从库的relay log索引文件复制到新从库，并设置新从库的– relay-log-index与原来从库的这个参数一致。如果原来从库没有设置–relay-log-index，把新从库的–relay-log-index设置为 existing_slave_hostname-relay-bin.index。
[root@localhost mysql]# vi /mysqlsoft/mysql/my.cnf relay-log=localhost-relay-bin relay-log-index=localhost-relay-bin.index
如果已经执行了后边步骤，在启动新从库的时候遇到这个问题，请执行以下步骤：
a.在新从库和原从库上执行stop slave
b.复制原来的从库的relay log索引文件到新从库，确保覆盖新从库的relay log索引文件。
c.执行后边的步骤。
3.复制原来从库的master info和relay log info文件到新从库。（这两个也可能存在表里边，跟如何配置的有关）这两个文件保存了当前的主 库binlog的坐标和从库relay log的坐标。
4.start原来的从库
[root@localhost mysql]# service mysqld start Starting MySQL.. SUCCESS!
5.编辑新从库的配置文件，给新从库配置一个不同于现在主从结构中服务器的server-id
[root@localhost mysql]# vi /mysqlsoft/mysql/my.cnf server-id=3
6.启动新从库。从服务器使用其主信息存储库中的信息启动复制进程。
[root@localhost mysql]# service mysqld start Starting MySQL.. SUCCESS!
但是原来的从库出现以下错误
2024-07-08T09:50:20.245669Z 5 [ERROR] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log: 'A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'binlog.000183' at 1395, the last event read from '/mysqldata/mysql/binlog.000183' at 1848, the last byte read from '/mysqldata/mysql/binlog.000183' at 1848.', Error_code: 1236 mysql> show variables like '%server%id%'; +----------------+--------------------------------------+ | Variable_name | Value | +----------------+--------------------------------------+ | server_id | 2 | | server_id_bits | 32 | | server_uuid | 1c5eb4fb-9479-11ec-8f21-005056a390e6 | +----------------+--------------------------------------+ 3 rows in set (0.00 sec) mysql> show variables like '%server%id%'; +----------------+--------------------------------------+ | Variable_name | Value | +----------------+--------------------------------------+ | server_id | 3 | | server_id_bits | 32 | | server_uuid | 1c5eb4fb-9479-11ec-8f21-005056a390e6 | +----------------+--------------------------------------+ 3 rows in set (0.90 sec)
因为新从库是用原来的从库复制而来，所以在${data_dir}/auto.cnf中的server-uuid值是一样的，要处理该问题只需要删除新从库中的auto.cnf文件，就会自动创建和生成新的server-uuid值。
[root@localhost mysql]# ll auto.cnf -rwxrwxr-x. 1 mysql mysql 56 Feb 23 2022 auto.cnf [root@localhost mysql]# rm -rf auto.cnf [root@localhost mysql]# ll auto.cnf ls: cannot access auto.cnf: No such file or directory [root@localhost mysql]# service mysqld start Starting MySQL..... SUCCESS! [root@localhost mysql]# ll auto.cnf -rw-r-----. 1 mysql mysql 56 Jul 8 18:07 auto.cnf mysql> show variables like '%server%id%'; +----------------+--------------------------------------+ | Variable_name | Value | +----------------+--------------------------------------+ | server_id | 3 | | server_id_bits | 32 | | server_uuid | f08c0957-3d11-11ef-bea6-005056b9a980 | +----------------+--------------------------------------+ 3 rows in set (0.00 sec)
验证主从同步
主库
mysql> create table t4(t_id int primary key not null,t_name varchar(30)); Query OK, 0 rows affected (0.02 sec) mysql> insert into t4 values(1,'jingyong'); Query OK, 1 row affected (0.01 sec) mysql> select * from t4; +------+----------+ | t_id | t_name | +------+----------+ | 1 | jingyong | +------+----------+ 1 row in set (0.00 sec)
原来从库
mysql> select * from t4; +------+----------+ | t_id | t_name | +------+----------+ | 1 | jingyong | +------+----------+ 1 row in set (0.00 sec)
新从库
mysql> select * from t4; +------+----------+ | t_id | t_name | +------+----------+ | 1 | jingyong | +------+----------+ 1 row in set (0.00 sec)