Shulou(Shulou.com)06/01 Report--

System environment and architecture

# Host name, system version, mysql version, ip address, mysqlMaster centos7.4 mysql5.7 192.168.1.42mysqlSlave centos7.4 mysql5.7 192.168.1.43#vip:192.168.1.41

Install the database on master and slave respectively

Yum install epel*-y & & yum clean all & & yum makecache rpm-Uvh http://repo.mysql.com/mysql57-community-release-el7.rpmyum clean all & & yum makecacheyum install gcc gcc-c++ openssl-devel mysql mysql-server mysql-devel-y

Create a database file storage path

Mkdir / data/mysql-pchown-R mysql:mysql / data/mysql

Configure mysql Profil

# configure mysql profile on mysqlMaster

Vi / etc/ my.cnf [mysqld] server-id = 1 # is globally unique. Log-bin = mysql-bin # log-bin means binary logging is enabled, mysql-bin indicates the naming format of the log file, and will generate mysql-bin.0001, etc. Relay-log = mysql-relay-bin # specify the relay log format (after pulling the main log Replicate-wild-ignore-table=mysql.% # specifies which libraries or tables are not synchronized, mysql is the library name,.% represents all of the following tables Mysql.user represents the user table under different mysql libraries replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%#replicate-wild-do-table=boke.% # indicates synchronizing that library # Note: do not use the binlog-do-db or binlog-ignore-db option on the master library # and do not use the replicate-do-db or replicate-ignore-db option on the slave library Because this may cause the problem of cross-library update failure. It is recommended to use replicate_wild_do_table and replicate_wild_ignore_table from the library to solve the replication filtering problem datadir=/data/mysqlsocket=/data/mysql/mysql.sockuser=mysqlsymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidcharacter-set-server=utf8 [mysql] socket=/data/mysql/mysql.sockdefault-character-set= UTF8 [client] socket=/data/ If mysql/mysql.sockdefault-character-set=utf8user=rootpassword=NCYD-tianyu@0791# doesn't write this field, Using the local command to enter mysql will report an error, indicating that the default path / var/lib/mysql/mysql.sock cannot be found.

Configure the mysql profile on mysqlSlave

Vi / etc/ my.cnf [mysqld] server-id = 2log-bin = mysql-binrelay-log = mysql-relay-binreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%datadir=/data/mysqlsocket=/data/mysql/mysql.sockuser=mysqlsymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidcharacter-set-server= UTF8 [MySQL] socket=/data/mysql/mysql.sockdefault-character-set= UTF8 [client] socket=/data / mysql/mysql.sockdefault-character-set=utf8user=rootpassword=NCYD-tianyu@0791

# if this field is not written, an error will be reported when entering mysql with the local command, indicating that the default path / var/lib/mysql/mysql.sock cannot be found. Initialize the database first (initialization is required to generate the necessary information in the / data/mysql directory)

# initialization method after mysql5.7.7 mysqld-- initialize-- user=mysql-- datadir=/data/mysql#mysql5.7.7 previous initialization method mysql_install_db-- user=mysql-- datadir=/data/mysql starts the master-slave database respectively # in centos7, you must first close selinux, and if you cannot start mysqldsystemctl start mysqld, do security reinforcement from mysql. # check to the previous step When mysql is started for the first time, the password automatically generated by the system (cat / var/log/mysqld.log | grep pass) mysql_secure_installation # uses this command to change the random root password (changed to NCYD-tianyu@0791) to manually synchronize data (if the current environment, the master already has data, and the slave is new) 1: create a user in the master database for replication And authorize mysql > GRANT REPLICATION SLAVE ON *. * to 'tongbu'@'192.168.1.43' identified by' 123456789' 2: lock the table on the main mysql (make all its tables read-only) mysql > flush tables with read lock # do not exit the terminal, or the lock will fail. 3: open another command line terminal and use tools such as myqldump to export the data (or directly package the directory where the mysql data is stored and send it to the slave) 4: copy the exported data to the slave, create a new library and import the data to each other to master each other (to achieve dual master mode)

Set mysqlSlave as your primary role server on mysqlMaster

Mysql > show master status; # View the status of the mysqlSlave (record the File name, and Position) change master tomaster_host = '192.168.1.43 mastercards user =' tongbu',master_password = '123456789 mastercalendar logbook file =' mysql-bin.000002',master_log_pos = 1006MySQL > start slave; # start the replication process on the slave side (some versions are: slave start;) mysql > show slave status\ G # to view the Iapot O process on the slave side, and the SQL process

Set mysqlMaster as your main role server on the slave

Mysql > show master status; # View the status of the mysqlMaster (record the File name, and Position) change master tomaster_host = '192.168.1.42 master master user =' tongbu',master_password = '123456789 master master logbook file =' mysql-bin.000002',master_log_pos = 1006 mystic SQL > start slave; # start the replication process mysql > show slave status\ G # on the slave side to view the Icano process on the slave side, and the SQL process

Verify synchronization

1: first verify whether the library is created on myssqlMaster and exists on mysqlSlave.

2: second, verify whether the library is created on mysqlSlave and exists on mysqlMaster.

Zhengzhou Infertility Hospital: http://jbk.39.net/yiyuanzaixian/zztjyy/

Install keepalived to achieve VIP switching to achieve high availability

Yum install keepalived-y # creates a check script to check the mysql on both mysqlMaster and mysqlSlave, and gives the execute permission touch / etc/keepalived/mysql_check.shchmod + x / etc/keepalived/mysql_check.shvi / ETC keepaliv _ ed.mysqlslave check.shslave _ check _ _ in the new version of myql If you write the password directly on the command line, you will be prompted for security. The solution is to write it in the [client] field in the configuration file of mysql. See the configuration file slave_is= ($(mysql-e "show slave status\ G" | grep "Slave_.*_Running" | awk'{print $2}') of mysql above) if ["${slave_is [0]}" = "Yes"-a "$slave_is [1]" = "Yes"] then exit 0else exit 1fi

# Note: keepalived checks the script vrrp_script and only recognizes two return values. 0 means normal, and non-0 means abnormal (this should be noted when writing a script). If it is abnormal, you need to make a switch.

Configure keepalived for high availability

Vi / etc/keepalived/keepalived.confglobal_defs {notification_email {acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc} notification_email_from Alexandre.Cassen@firewall.loc smtp_server 192.168.200.1 smtp_connect_timeout 30 router_id LVS_DEVEL vrrp_skip_check_adv_addr # vrrp_strict # Note: when you find that you can't ping virtual VIP It is recommended to comment out the script vrrp_garp_interval 0 vrrp_gna_interval 0} vrrp_script mysql_check {script "/ etc/keepalived/mysql_check.sh" #. If you find that it is not executed, you can check his permission. 777 is not allowed, and the log prompt is not safe. You can simply chmod + x / etc/keepalived/mysql_check.sh # script "shutdown-r now" # or Debug interval 2 weight 2} vrrp_instance VI_1 {state MASTER # mysqlMaster directly with the command MASTER On mysqlSlave, it's BACKUP interface ens192 virtual_router_id 52. Priority 100 # mysqlMaster is 100. On mysqlSlave, 90 advert_int 1 nopreempt # configuration VIP (Note: failover preemptive mode, turn it off as far as possible, default is on This parameter only needs to be set on a high priority) authentication {auth_type PASS auth_pass 1111} track_script {# call the script mysql_check} virtual_ipaddress {192.168.1.41and24 dev ens192} defined above to check mfsmaster

Start keepalived

Systemctl start keepalived

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.