MySQL数据库主从复制原理及作用分析_Mysql_

 #二进制安装：https://blog.csdn.net/qq_47945825/article/details/116848970?spm=1001.2014.3001.5501 #或者网络仓库安装：（一般二进制安装） https://blog.csdn.net/qq_47945825/article/details/116245442?spm=1001.2014.3001.5501 

 [root@mysql01 ~]# mysql -uroot -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ [root@mysql02 ~]# mysql -uroot -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 

 mysql> create user 'vvv'@'192.168.136.191' identified by 'vvv0917'; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave on *.*to 'vvv'@'192.168.136.191'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 

 [root@mysql02 ~]# mysql -uvvv -vvv0917 -h192.168.136.145 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) 

 [root@mysql01 ~]# cat /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve log-bin=mysql_bin #启动binlog日志 server-id=10 #数据库服务器唯一标识，id必须比从数据库小 #重启服务 （此重启方式，前提已配置mysqld.service文件） [root@mysql01 ~]# systemctl restart mysqld 观察主数据库状态： mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql_bin.000004 | 962 | | | | +------------------+----------+--------------+------------------+--- 

 [root@mysql02 ~]# cat /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3307 user = mysql pid-file = /opt/data/mysql.pid skip-name-resolve #skip-grant-tables server-id=20 #服务器id，大于主数据库id relay-log=mysql_relay_log #启动中继日志 #log-bin=mysql-bin #重启服务： [root@mysql02 ~]# systemctl restart mysqld 

 [root@slave02 ~]# mysql -uroot -p mysql> change master to -> master_host='192.168.136.145', -> master_user='vvv', -> master_password='vvv0917', -> master_log_file='mysql_bin.000004', -> master_log_pos=962; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; #stop slave为关闭 Query OK, 0 rows affected (0.01 sec) #查看配置状态： mysql> show slave status\G; Slave_IO_State: Waiting for master to send event Master_Host: 192.168.136.145 Master_User: vvv Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.000004 Read_Master_Log_Pos: 962 Relay_Log_File: mysql_relay_log.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql_bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes #此处必须俩个都是yes，就是配置成功，否则失败 

 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 

 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 

 mysql> create database clq; Query OK, 1 row affected (0.00 sec) mysql> create table clq01(id int(11)not null primary key auto_increment,name varchar(100)not null,age tinyint(4)); mysql> insert clq01(name,age) values('A',20),('B',21),('C',22); Query OK, 3 rows affected (0.00 sec)

 mysql> select * from clq01; +----+------+------+s | id | name | age | +----+------+------s+ | 1 | A | 20 | | 2 | B | 21 | | 3 | C | 22 | +----+------+------+ #主从复制完成！

 flush tables with read lock： quit：退出即可为解锁（备份完之后才能解锁）

 #先对主库进行全备 [root@mysql01 ~]# mysqldump -uroot -A > all-databases.sql #拷贝数据到从数据库上 [root@mysql01 ~]# ls /clq all-databases.sql [root@mysql01 ~]# scp /clq/all-databases.sql root@192.168.136.193:/clq/ The authenticity of host '192.168.136.193 (192.168.136.193)' can't be established. ECDSA key fingerprint is SHA256:XIAQEoJ+M0vOHmCwQvhUdw12u5s2nvkN0A4TMKLaFiY. Are you sure you want to continue connecting (yes/no/[fingerprint])yes root@192.168.136.193's password: all-databases.sql 100% 853KB 115.4MB/s 00:00 [root@mysql02 clq]# ll 总用量 896 #从库上查看 -rw-r--r--. 1 root root 873266 5月 17 19:36 all-databases.sql 

 [root@mysql02 clq]# mysql -uroot -pHuawei0917@ < all-databases.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@mysql02 clq]# mysql -uroot -pHuawei0917@ -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | clq | | mysql | | performance_schema | | sys | +--------------------+ 主库： mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | clq | | mysql | | performance_schema | | sys | +--------------------+ 

 [root@mysql01 ~]# cat /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：MySQL 基础常用命令总结_Mysql_
                                                                
下一篇：mysql5.7单实例自启动服务配置过程_Mysql_