Build a high-performance MySQL database system

Lab Environment:

db1:172.16.1.100

Db2:172.16.1.101

vrrip:172.16.1.99

Steps:

Yum-y install MySQL

1. Modify the MySQL configuration file for DB1

Server-id = 2 #DB1和DB2的id must be different log-bin=mysql-bin  #开启mysql的二进制日志功能relay-log = mysql-relay-bin # Define the naming format for the trunk log replicate-wild-ignore-table=mysql.% #不复制的数据库或表replicate-wild-ignore-table=test.% #不复制的数据库或表
replicate-wild-ignore-table=information_schema.% #不复制的数据库或表

2. Create a replication user in DB1 and authorize

Grant Replication Slave on * * to  "Chen" @ "172.16.1.101" identified by "123456";
Show master status;
Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |                    106 |　　　　　　　　　  | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

3. Set DB1 as its primary server in DB1 MySQL library

MySQL > Change master to         master_host= "172.16.1.100",        master_user= "Chen",        master_password= "123456",        master_log_file= "mysql-bin.000006",        master_log_pos=106;        

4. Then run

mysql > Start slave;

5. Query DB1 Running Status

MySQL > show slave status;mysql> show slave status\g;*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos              t:172.16.1.100 Master_user:chen master_port:3306 connect_retry:60 master_log_file:mysql-bin.000009 read_master_log_pos:106 Relay_log_file:mysql-relay-bi n.000021 relay_log_pos:251 relay_master_log_file:mysql-bin.000009Slave_io_running:yes Slave_sql_running:yesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replic                   ate_wild_do_table:replicate_wild_ignore_table:mysql.%,test.%,information_schema.% last_errno:0  last_error:skip_counter:0 exec_master_log_pos:106 Relay_log_space: 551 Until_condition:none until_log_file:until_log_pos:0 MASTER_SS L_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Mast                Er_ssl_cipher:master_ssl_key:seconds_behind_master:0master_ssl_verify_server_cert:no last_io_errno:0 last_io_error:last_sql_errno:0 last_sql_error:1 Row i N Set (0.00 sec) Error:no query specified

6. Then configure the above configuration on the DB1

7. Download keepalived on DB1 and DB2

Yum Install keepalived

8. Configuring the Keepalived configuration file

[Email protected] ~]# vim/etc/keepalived/keepalived.conf

[[email protected] ~]# vim/etc/keepalived/keepalived.conf auth_type PASS auth_pass 1111} virtual_ipaddress {172.1              6.1.99}}virtual_server 172.16.1.99 3306 {delay_loop 2 #lb_algo RR #LVS算法, we shut down #lb_kind DR #LVS模式, if you do not shut down, the standby server cannot connect through the VIP master MySQL persistence_timeout #同一IP的连接60秒内被分配到同一台真实服务器 protocol TCP real_server 172.16.1.1 3306 {#检测本地mysql, Backup will also be written to detect local MySQL weight 3 notify_down/usr/local/keepalived/mysql.sh #当mysq服down时, execute this script, kill Keepa lived implementation switch Tcp_check {connect_timeout 3 #连接超时 nb_get_retry 3 #重试次数 delay_before_retry 3 #重试间隔时间}} "/etc/keepalive D/keepalived.conf "40L, 1292C written[[email protected] ~]# vim/etc/keepalived/keepalived.conf auth_type PASS              Auth_pass 1111} virtual_ipaddress {172.16.1.99}}virtual_server 172.16.1.99 3306 {delay_loop 2 #lb_algo RR #LVS算法, we shut down the #lb_kind DR #LVS模式, if not, the standby server cannot connect to the main MySQL persistence_timeout by VIP #同一IP的连接60秒内被分配到同一台 Real Server Protocol TCP Real_server 172.16.1.101 3306 {#检测本地mysql, Backup will also be written to detect local MySQL weight 3 notify_down/usr/local/keepalived/mysql.sh #当mysq服down时, execute This script kills keepalived implementation switch Tcp_check {connect_timeout 3 #连接超时 nb_get_retry 3 #重试次数 delay_before_retry 3 #重试间隔时间}}

　　

　

Build a high-performance MySQL database system