自建主从复制-mycat · 服务器学习

[TOC] 参考: https://www.cnblogs.com/YingYue/p/7677814.html?tdsourcetag=s_pctim_aiomsg # （一）主库和从库前期准备目的：保证主库和从库的连接配置是正确的！ ## 1、my.ini配置 >注：windows是my.ini，Linux是my.conf ### 主库配置 ``` server-id = 1 #[必须]服务器唯一ID，默认是1 log-bin = mysql-bin #[必须]启用二进制日志 binlog-do-db = test_db #指定对名称为test_db的数据库记录二进制日志 # binlog-ignore-db = mysql #指定不对名称为mysql的数据库记录二进制日志 # binlog-ignore-db = information_schema #指定不对名称为information_schema的数据库记录二进制日志 # binlog_format = mixed #binlog日志格式，mysql默认采用，如果从服务器slave有别的slave要复制那么该slave也需要这一项# expire_logs_days = 7 #超过7天的binlog删除### skip-external-locking #MySQL选项以避免外部锁定。该选项默认开启 default-storage-engine = InnoDB #默认存储引擎 lower_case_table_names = 1 #忽略表大小写 ``` 重启 `service mysqld restart` ### 从库配置 ``` server-id = 2 #[必须]服务器唯一ID，默认是1 replicate-do-db = test_db #复制名称为test_db的数据库 replicate-ignore-db = mysql #不需要（忽略）复制名称为mysql的数据库 # replicate-ignore-db = information_schema #不需要（忽略）复制名称为information_schema的数据库 skip-external-locking #MySQL选项以避免外部锁定。该选项默认开启 default-storage-engine = InnoDB #默认存储引擎 lower_case_table_names = 1 #忽略表大小写 ``` 重启 `service mysqld restart` ## 2、访问权限（这一步很关键，多数都死在这个地方） > 目的：保证主从库通信正常，即：主库可以访问从库地址，从库可以访问主库。即： mysql -h IP1 -u root -p，mysql -h IP2 -u root -p，可以正常访问。鉴于有些人不知道，每个数据库服务器SQL做以下代码 ``` mysql -u root use mysql; update user set host = '%' where user = 'root'; select host, user from user; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; FLUSH PRIVILEGES ``` > 例如1：防火墙都关闭，如ECS把安全组建立连接！ > 例如2：将数据库通信正常，授权如：grant all privileges命令！相关百度即可！ > 注意1：主库创建数据库，从库不需要创建！主从库做好后，从库数据库会自动创建！ > 注意2：主库用户为：root,从库用户也为：root ### a.主库访问用户及数据库 ``` create database test_db; //创建数据库 grant all privileges on test_db.* to 'root'@'%' identified by ''; //授权，密码为空 flush privileges; grant replication slave on *.* to 'root'@'%'; //给ip地址为所有（%表示任何ip）MySQL服务器上的盈月授权对该master的复制权限 flush privileges; ``` ### b.从库访问用户及数据库从库只需要创建数据库 ``` create database test_db; //创建数据库 ``` # （二）从库运行先运行主库，再看从库状态！ ## 1、主从库状态 ``` show master status; /*!查看主master的状态，如果没有数据，需要在my.cnf 中配置*/ ``` 显示： ``` mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000001 Position: 154 Binlog_Do_DB: test_db Binlog_Ignore_DB: mysql,information_schema Executed_Gtid_Set: 1 row in set (0.00 sec) ``` ## 1、从库连接主库并启动 ``` show slave status; //查看该slave的状态 stop slave; //停止从服务器 reset slave; //重置从服务器 change master to master_user='root', master_password='', master_host='192.168.100.101',master_port=3306, master_log_file='mysql-bin.000001',master_log_pos=154; //注：mysql-bin.000002是上边的file,这个2071是上边的Position start slave; //开启从服务器 show slave status\G //查看从服务器连接状态 ``` 保证结果中有两个状态是正常的，如下（**如果不是Yes，说明通信肯定有问题，继续找问题**）： ``` Slave_IO_Running: Yes Slave_SQL_Running: Yes ``` >启动后，从库的数据库test_db会自动生成！ # （三）测试运行 ## 1、创建增删改（主库） ``` create table country(id int not null primary key auto_increment, name varchar(100)); insert into country values(1, '南非'),(2,'瑞典'); delete from country where id = 1; update country set name='中国' where id =2; ``` ## 2、查（从库） ``` select * from country where id = 2; ``` # （四）数据库代理-mycat运行官网： http://www.mycat.io/ >注间：Linux需要安装 java 环境，详见《CentOS安装JDK的四种方法，推荐yum安装》 ## 1、配置server.xml（连接地址） >注：这里指的是连接的用户名和密码！ ``` <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mycat:server SYSTEM "server.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"> <system> <property name="useSqlStat">1</property>  </system> <user name="root"> <property name="password">123456</property> <property name="schemas">TESTDB</property> </user> <user name="yingyue"> <property name="password">123456</property> <property name="schemas">TESTDB</property> </user> </mycat:server> ``` ## 2、配置schema.xml（读写库地址） ``` <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema> <dataNode name="dn1" dataHost="localhost1" database="test_db" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat>  <writeHost host="hostM1" url="192.168.100.101:3306" user="root" password="">  <readHost host="hostS2" url="192.168.100.103:3306" user="yingyue" password="123456" /> </writeHost> </dataHost> </mycat:schema> ``` ## 3、启动与连接 ``` mycat install mycat start mysql -h 192.168.100.101 -uroot -p123456 -P8066 -DTESTDB //可以正常连接mycath中间件了 (或)mysql -h 192.168.100.101 -uyingyue -p123456 -P8066 -DTESTDB ``` # （五）PHP连接mycat地址 ``` $TESTDB = [ // 数据库类型 'type' => 'mysql', // 服务器地址 'hostname' => '192.168.100.101', // 数据库名 'database' => 'TESTDB', // 用户名 'username' => 'root', // 密码 'password' => '123456', // 端口 'hostport' => '8066', }; return $TESTDB; ``` # 错误解决入处理 ## 1、Last_IO_Error: error connecting to master 'root@1.1.1.1:3306' - retry-time: 60 retries: 12 答：缺少这一步操作： `grant replication slave on *.* to 'root'@'%'; ` ## 2、所有操作都操作完了，Slave_IO_Running: No 依然为No 答：我居然是主从库的配置错了，晕死我了！ ## 3、从库数据中断怎么办？从库数据中断就必须得先从主库把数据恢复，再进行同步操作！