MySQL

1.MySQL总体知识思维导图

2.数据库主从复制

1.开启二进制日志（主库）

[root@localhost ~]# vim /etc/my.cnf

server_id=10
log_bin=master

2.创建允许从库连接的复制用户

mysql> CREATE USER 'repluser'@"192.168.1.111" IDENTIFIED BY 'WWW.1.com';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT replication slave ON *.* TO 'repluser'@'192.168.1.111';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

3.主库上进行数据备份，将备份文件拷贝到从库

[root@localhost ~]# mysqldump -uroot -pWWW.1.com --lock-all-tables --master-data=2 --all-databases > /opt/data.sql

[root@localhost ~]# scp /opt/data.sql  root@192.168.1.111:/opt/

4.配置server_id、二进制日志（从库）

vim /etc/my.cnf
[mysqld]
server_id=11
log_bin=master

5.恢复备份

[root@zabbix_agent ~]# mysql -uroot -pWWW.1.com < /opt/data.sql 

6.指定连接主库的信息

mysql> CHANGE MASTER TO
    -> MASTER_HOST="192.168.1.100",
    -> MASTER_USER="repluser",
    -> MASTER_PASSWORD="WWW.1.com",
    -> MASTER_LOG_FILE="master.000010",
    -> MASTER_LOG_POS=781;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

7.启动复制线程

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

8.验证复制线程启动成功

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.100
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000010
          Read_Master_Log_Pos: 1056
               Relay_Log_File: zabbix_agent-relay-bin.000002
                Relay_Log_Pos: 592
        Relay_Master_Log_File: master.000010
             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: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1056
              Relay_Log_Space: 806
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 5f753038-103a-11ef-994d-000c29cc6b2f
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

3.Mycat实现读写分离

1.下载软件

yum install -y jdk1.8
yum install -y mycat

2.添加环境变量

vim /etc/profile

export JAVA_HOME=/usr/local/jdk1.8.0_91
export PATH=$PATH:$JAVA_HOME/bin
export MYCAT_HOME=/usr/local/mycat

source /etc/profile

3.编辑schema.xml文件，定义读写分离

[root@localhost ~]# cat /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	// 定义虚拟数据库，关联dataNode
	<schema name="game" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn01">
	</schema>

	// 定义真实数据库，关联dataHost
	<dataNode name="dn01" dataHost="dh01" database="game" />

	// 定义数据主机，主库、从库地址
	<dataHost name="dh01" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="192.168.1.100:3306" user="mycatuser"
				   password="WWW.1.com">
		</writeHost>
		<writeHost host="hostS1" url="192.168.1.111:3306" user="mycatuser"
				   password="WWW.1.com" />
	</dataHost>
</mycat:schema>

4.在后端数据库建立允许mycat连接的用户

mysql> CREATE USER 'mycatuser'@"192.168.1.120" IDENTIFIED BY 'WWW.1.com';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT all ON game.* TO 'mycatuser'@"192.168.1.120";
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

5.在MyCAT上创建允许业务连接的用户

[root@localhost conf]# vim /usr/local/mycat/conf/server.xml 

        <user name="admin">
                <property name="password">123456</property>
                <property name="schemas">game</property>
        </user>