时间:2021-07-01 10:21:17 帮助过:4人阅读
- mysql><span style="color: #000000;"> use mysql;
- Reading table information </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> completion of table and column names
- You can turn off this feature to get a quicker startup with </span>-<span style="color: #000000;">A
- Database changed
- mysql</span>> <span style="color: #0000ff;">select</span><span style="color: #000000;"> user,host from user;
- </span>+---------------+-----------+
- | user | host |
- +---------------+-----------+
- | repl | % |
- | root | % |
- | mysql.session | localhost |
- | mysql.sys | localhost |
- +---------------+-----------+
- <span style="color: #800080;">4</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)
- mysql> show variables like <span style="color: #800000;">‘</span><span style="color: #800000;">%server_id%</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
- </span>+----------------+-------+
- | Variable_name | Value |
- +----------------+-------+
- | server_id | <span style="color: #800080;">0</span> |
- | server_id_bits | <span style="color: #800080;">32</span> |
- +----------------+-------+
如果配置文件没有设置server_id参数,则默认都是0
编辑/etc/my.cnf
添加service_id,它的值可以跟服务器的IP最后一位数字一样,这样就能保证内网中的服务器ID不重复。master上
- server_id=<span style="color: #800080;">103</span><span style="color: #000000;">
- log</span>-bin=<span style="color: #000000;">master
- binlog_format</span>=row
slave上
- server_id=<span style="color: #800080;">69</span>
在主库上操作
- mysql><span style="color: #000000;"> show databases;
- </span>+--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | sys |
- | test |
- +--------------------+
- <span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)
主库有一个test数据库
- mysql><span style="color: #000000;"> show tables;
- </span>+----------------+
- | Tables_in_test |
- +----------------+
- | test |
- +----------------+
- <span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)
有一个test表
- mysql> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from test;
- </span>+------+------+
- | <span style="color: #0000ff;">id</span> | name |
- +------+------+
- | <span style="color: #800080;">1</span> | aaaa |
- +------+------+
- <span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)
表里有一条数据,开始备份主库
- [root@node2 data]# mysqldump -uroot -p --all-databases > /home/mysql-<span style="color: #800080;">5.7</span>.<span style="color: #800080;">26</span>/bak/bak.sql<br>[root@node2 data]# scp -P25601 /home/mysql-5.7.26/bak/bak.sql root@172.28.18.69:/home/mysql-5.7.26/bak/<br><br>在从库上操作,恢复主库数据
- [root@localhost log]# mysql -uroot -p < /home/mysql-<span style="color: #800080;">5.7</span>.<span style="color: #800080;">26</span>/bak/<span style="color: #000000;">bak.sql
- Enter password:
- [root@localhost log]# mysql </span>-uroot -<span style="color: #000000;">p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection </span><span style="color: #0000ff;">id</span> is <span style="color: #800080;">4</span><span style="color: #000000;">
- Server version: </span><span style="color: #800080;">5.7</span>.<span style="color: #800080;">26</span><span style="color: #000000;"> MySQL Community Server (GPL)
- Copyright (c) </span><span style="color: #800080;">2000</span>, <span style="color: #800080;">2019</span>, Oracle and/<span style="color: #000000;">or its affiliates. All rights reserved.
- Oracle is a registered trademark of Oracle Corporation and</span>/<span style="color: #000000;">or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type </span><span style="color: #800000;">‘</span><span style="color: #800000;">help;</span><span style="color: #800000;">‘</span> or <span style="color: #800000;">‘</span><span style="color: #800000;">\h</span><span style="color: #800000;">‘</span> <span style="color: #0000ff;">for</span> help. Type <span style="color: #800000;">‘</span><span style="color: #800000;">\c</span><span style="color: #800000;">‘</span> to <span style="color: #0000ff;">clear</span><span style="color: #000000;"> the current input statement.
- mysql</span>><span style="color: #000000;"> show databases;
- </span>+--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | sys |
- | test |
- +--------------------+
- <span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
- mysql</span>>
此时,从库里test数据库有了,里面也有test数据表里记录
- mysql><span style="color: #000000;"> show databases;
- </span>+--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | sys |
- | test |
- +--------------------+
- <span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
- mysql</span>><span style="color: #000000;"> use test;
- Reading table information </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> completion of table and column names
- You can turn off this feature to get a quicker startup with </span>-<span style="color: #000000;">A
- Database changed
- mysql</span>> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from test
- </span>-><span style="color: #000000;"> ;
- </span>+------+------+
- | <span style="color: #0000ff;">id</span> | name |
- +------+------+
- | <span style="color: #800080;">1</span> | aaaa |
- +------+------+
- <span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.01</span> sec)
首先获取主库日志文件名称和偏移量
- mysql><span style="color: #000000;"> show master status \G;
- </span>*************************** <span style="color: #800080;">1</span>. row ***************************<span style="color: #000000;">
- File: master.</span><span style="color: #800080;">000001</span><span style="color: #000000;">
- Position: </span><span style="color: #800080;">154</span><span style="color: #000000;">
- Binlog_Do_DB:
- Binlog_Ignore_DB:
- Executed_Gtid_Set:
- </span><span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
- ERROR:
- No query specified
- mysql</span>>
在从库上执行
- mysql><span style="color: #000000;"> change master to
- </span>-> master_host=<span style="color: #800000;">‘</span><span style="color: #800000;">172.28.18.103</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
- </span>-> master_port=<span style="color: #800080;">3306</span><span style="color: #000000;">,
- </span>-> master_user=<span style="color: #800000;">‘</span><span style="color: #800000;">repl</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
- </span>-> master_password=<span style="color: #800000;">‘</span><span style="color: #800000;">Zaq1xsw@</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
- </span>-> master_log_file=<span style="color: #800000;">‘</span><span style="color: #800000;">master.000001</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
- </span>-> master_log_pos=<span style="color: #800080;">154</span><span style="color: #000000;">;
- Query OK, </span><span style="color: #800080;">0</span> rows affected, <span style="color: #800080;">2</span> warnings (<span style="color: #800080;">0.21</span> sec)
启动从库
- mysql><span style="color: #000000;"> start slave;
- Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.01</span> sec)
查看从库状态
- mysql><span style="color: #000000;"> show slave status\G;
- </span>*************************** <span style="color: #800080;">1</span>. row ***************************<span style="color: #000000;">
- Slave_IO_State: Waiting </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> master to send event
- Master_Host: </span><span style="color: #800080;">172.28</span>.<span style="color: #800080;">18.103</span><span style="color: #000000;">
- Master_User: repl
- Master_Port: </span><span style="color: #800080;">3306</span><span style="color: #000000;">
- Connect_Retry: </span><span style="color: #800080;">60</span><span style="color: #000000;">
- Master_Log_File: master.</span><span style="color: #800080;">000001</span><span style="color: #000000;">
- Read_Master_Log_Pos: </span><span style="color: #800080;">154</span><span style="color: #000000;">
- Relay_Log_File: localhost</span>-relay-bin.<span style="color: #800080;">000002</span><span style="color: #000000;">
- Relay_Log_Pos: </span><span style="color: #800080;">317</span><span style="color: #000000;">
- Relay_Master_Log_File: master.</span><span style="color: #800080;">000001</span><span style="color: #000000;">
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
- Last_Error:
- Skip_Counter: </span><span style="color: #800080;">0</span><span style="color: #000000;">
- Exec_Master_Log_Pos: </span><span style="color: #800080;">154</span><span style="color: #000000;">
- Relay_Log_Space: </span><span style="color: #800080;">528</span><span style="color: #000000;">
- Until_Condition: None
- Until_Log_File:
- Until_Log_Pos: </span><span style="color: #800080;">0</span><span style="color: #000000;">
- Master_SSL_Allowed: No
- Master_SSL_CA_File:
- Master_SSL_CA_Path:
- Master_SSL_Cert:
- Master_SSL_Cipher:
- Master_SSL_Key:
- Seconds_Behind_Master: </span><span style="color: #800080;">0</span><span style="color: #000000;">
- Master_SSL_Verify_Server_Cert: No
- Last_IO_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
- Last_IO_Error:
- Last_SQL_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
- Last_SQL_Error:
- Replicate_Ignore_Server_Ids:
- Master_Server_Id: </span><span style="color: #800080;">103</span><span style="color: #000000;">
- Master_UUID: ddbee8c3</span>-76da-11e9-<span style="color: #800080;">9174</span>-<span style="color: #000000;">90b11c15be09
- Master_Info_File: </span>/home/mysql-<span style="color: #800080;">5.7</span>.<span style="color: #800080;">26</span>/data/master.<span style="color: #0000ff;">info</span>
此时:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主从复制配置已经生效
在主库插入一条数据
- mysql> insert test values(<span style="color: #800080;">2</span>,<span style="color: #800000;">‘</span><span style="color: #800000;">bbbb</span><span style="color: #800000;">‘</span><span style="color: #000000;">);
- Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.03</span> sec)
从库上查询
- mysql> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from test;
- </span>+------+------+
- | <span style="color: #0000ff;">id</span> | name |
- +------+------+
- | <span style="color: #800080;">1</span> | aaaa |
- | <span style="color: #800080;">2</span> | bbbb |
- +------+------+
- <span style="color: #800080;">2</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)
数据已经复制成功了。
mysql5.7.26做主从复制配置
标签:variable aaa base 不同的 查看 ref 也有 etc hang