Mysql

Create database, database table and insert data:

• Create database chinaskill
• Create the testable table in the chinaskill database
• Create the following fields:
  • id int non empty primary key
  • Teamname string max. 50
  • remarks string maximum 255
• Insert the following data:
  • id 1
  • Teamname "cloud"
  • remarks "chinaskill"
• Query Teamname field with id 1

create database IF NOT EXISTS chinaskill;
use chinaskill;
create table testable (id int not null primary key, Teamname varchar(50), remarks varchar(255));
insert into testable values (1, "cloud", "chinaskill");
select Teamname, remarks from testable where id = 1;

Set permissions in the database:

• Set all local permissions of all tables in all databases of root user, with password 000000
• Set the slave permissions of all tables mysqk2 hosts in all databases of user with password 000000
• Change the master hostname of the slave database to mysql1, the user name to user, and the password to 000000

grant all privileges on *.* to root@'%' identified by '000000' with grant option;
grant replication slave on *.* to 'user'@'mysql2' identified by '000000';
change master to master_host='mysql1',master_user='user',master_password='000000';

Master-slave database

Create two virtual machines mysql1 and mysql2 and configure the hosts file

hosts file:

192.168.200.3 mysql1
192.168.200.6 mysql2

Upload the mariadb-repo.tar.gz file provided by the attachment to the virtual machine through scp from the controller side

scp mariadb-repo.tar.gz 192.168.200.3:/root/
scp mariadb-repo.tar.gz 192.168.200.6:/root/

Extract it to the / opt directory

tar -zxvf mariadb-repo.tar.gz -C /opt/

local.repo

[mariadb]
name=mariadb
baseurl=file:///opt/mariadb-repo
enabled=1
gpgcheck=0

Check whether the yum source is configured correctly using yum repolist

Install mariadb and mariadb server

yum -y install mariadb mariadb-server

Start database systemctl start mariadb

Initialize database mysql_secure_installation

enter for none

y set password 000000

y n y y

Write the server.cnf file or my.cnf file on both sides at the same time

vi /etc/my.cnf.d/server.cnf or vi /etc/my.cnf

[mysqld]
bind-address=0.0.0.0 ; mysql binding IP
log_bin = mysql-bin ; record mysql log file
binlog_ignore_db = mysql ; No record mysql System library (specify) binlog-do-db and replicate-do-db (do not write when writing)
server_id = 1 ; Generally ip The fourth paragraph address requires different numbers!
binlog-do-db=chinaskill ; record chinaskill Database logs, mysql1 end
replicate-do-db=chinaskill ; copy chinaskill Database logs, mysql2 end

Restart mariadb on both sides and enter the database

systemctl restart mariadb

mysql -uroot -p000000

At the master database

grant all privileges on *.* to root@'%' identified by '000000' with grant option;
grant replication slave on *.* to 'user'@'mysql2' identified by '000000';

At slave database

change master to master_host='mysql1',master_user='user',master_password='000000';
start slave;
show slave status\G;

If error 1201 (HY000) is encountered: could not initialize master info structure for ''; More error messages can be found in the MariaDB error log;

Finally, create and delete data in the chinaskill database of the master node to see whether the slave node creates and deletes data. If so, the master-slave database configuration is successful!

Database read / write separation

Create virtual machine mycat

Upload the attached mariadb-repo.tar.gz, Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz and schema.xml files to the virtual machine through scp from the controller

scp mariadb-repo.tar.gz 192.168.200.8:/root/
scp Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz 192.168.200.8:/root/
scp schema.xml 192.168.200.8:/root/

Unzip -zxvf mariadb-repo.tar.gz to the / opt directory

tar -zxvf mariadb-repo.tar.gz -C /opt/

local.repo

[mariadb]
name=mariadb
baseurl=file:///opt/mariadb-repo
enabled=1
gpgcheck=0

Check whether the yum source is configured correctly using yum repolist

Install java-1.8.0-openjdk and java-1.8.0-openjdk devel

Use yum list | grep java - to get the package name

yum -y install java-1.8.0-openjdk java-1.8.0-openjdk-devel

Execute java -version to ensure successful java installation

Unzip Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz to / usr/local directory

tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/

Give / user/local/mycat directory permission

chmod -R 777 /usr/local/mycat/

schema.xml file, modify the url and password, and ensure that the database test has been created

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="USERDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema> 
<dataNode name="dn1" dataHost="localhost1" database="test" />  
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" dbType="mysql" dbDriver="native" writeType="0" switchType="1"  slaveThreshold="100">  
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="192.168.200.3:3306" user="root" password="000000">
        <readHost host="hostS1" url="192.168.200.6:3306" user="root" password="000000" />
    </writeHost>
</dataHost>
</mycat:schema>
<!--
schema name: Virtual database name.
sqlMaxLimit: Configure the default number of queries.
database: Is the real database name.
balance="0": The read / write separation mechanism is not enabled, and all read operations are sent to the currently available writeHost Come on.
balance="1": all-out readHost And stand by writeHost participate in select Statement load balancing, in short, when dual master and dual slave mode( M1->S1，M2->S2，also M1 And M2 Mutual primary and standby), under normal circumstances, M2,S1,S2 All involved select Load balancing of statements.
balance="2": All read operations are random writeHost,readhost Distributed on.
balance="3": All read requests are randomly distributed to wiriterHost Corresponding readhost Execution, writerHost Don't bear reading pressure, pay attention balance=3 Only in 1.4 And later versions have, 1.3 No version.
writeType="0": All writes are sent to the first configured writeHost，The first one hangs and needs to switch to the second one that still exists writeHost，After restart, the switch shall prevail, and the switch shall be recorded in the configuration file dnindex.properties Yes.
writeType="1": All writes are randomly sent to the configured writeHost. 
-->

Forcibly overwrite the schema.xml file in the / usr/local/mycat/conf / directory

\cp -f schema.xml /usr/local/mycat/conf/

The test database should be created in two databases

Modify permissions for schema.xml file

chown root:root /usr/local/mycat/conf/schema.xml

Modify the server.xml file in the / usr/local/mycat/conf / directory

vi /usr/local/mycat/conf/server.xml

Modify the end of the document

 <user name="root">
                <property name="password">000000</property>
                <property name="schemas">USERDB</property>

Delete end of file

<user name="user">
               <property name="password">user</property>
               <property name="schemas">TESTDB</property>
               <property name="readOnly">true</property>
       </user>

Start mycat service / bin/bash /usr/local/mycat/bin/mycat start

If the open ports 8066 and 9066 are queried with netstat -ntpl command, the service is started successfully. Otherwise, carefully check the configuration file: whether the schemas virtual database name does not correspond, whether the password has not been changed correctly, whether the user related information has not been deleted, whether the url address is not written correctly, whether the database real database has not been created, etc.

Install MariaDB client service Yum -y install MariaDB client (in uppercase)

Enter the mysql client mysql -h127.0.0.1 -P8066 -uroot -p0000000

You can see the status of database read-write separation from port 9066: MySQL - h127.0.0.1 - p9066 - uroot - p0000000 - E 'show @ @ datasource;'

Install MariaDB client service Yum -y install MariaDB client (in uppercase)

Enter the mysql client mysql -h127.0.0.1 -P8066 -uroot -p0000000

You can see the status of database read-write separation from port 9066: MySQL - h127.0.0.1 - p9066 - uroot - p0000000 - E 'show @ @ datasource;'