MySQL database master-slave configuration

This experiment requires 3 sets of centos6.5
192.168.204.139 # "Master" MySQL
192.168.204.138 # "from" MySQL

192.168.294.129 #lamp website

Please close the firewall for 3 machines, and SELinux

# Turn off the firewall

/etc/init.d/iptables stop

Chkconfig iptables off
Chkconfig--list |grep iptables

# Turn off SELinux
Sed-i/selinux/s/enforcing/disabled/g/etc/selinux/config

==================================================

# configure MySQL "primary" server 192.168.204.139

Yum Install Mysql-server mysql-y

Backup MySQL configuration file
cp/etc/my.cnf/etc/my.cnf_bak20170915

# Modify/ETC/MY.CNF as follows:
------------------------------------------
[Mysqld]

DataDir =/data/mydata
Socket=/var/lib/mysql/mysql.sock
User=mysql
Symbolic-links=0
Log-bin=mysql-bin
Server-id=1
Auto_increment_offset=1
auto_increment_increment=2

[Mysqld_safe]
Log_error=/var/log/mysqld.err
Pid-file=/var/run/mysqld/mysqld.pid
Replicate-do-db=all

------------------------------------------
Create the/data/mysql Data directory,
Mkdir-p/data/mysql
Chown-r Mysql:mysql/data/mysql

# restart MySQL,
/etc/init.d/mysqld restart

If restarting MySQL fails, see if selinux shuts down ******

MySQL default no password, command line input MySQL can enter the database

# Set permissions on the "primary" server and execute the following command:
> Grant replication Slave on * * to [e-mail protected] '% ' identified by ' 123123 ';
> Flush Privileges;

> Show master Status;
There should be a "map" here.

# configure MySQL from "from" Server 192.168.204.138

Yum Install Mysql-server mysql-y

Mv/etc/my.cnf/etc/my.cnf.bak

Vim/etc/my.cnf
------------------------------------------
[Mysqld]
DataDir =/data/mydata
Socket=/var/lib/mysql/mysql.sock
User=mysql
Symbolic-links=0
Log-bin=mysql-bin
#
server-id=2
auto_increment_offset=2
#
auto_increment_increment=2

[Mysqld_safe]
Log_error=/var/log/mysqld.err
Pid-file=/var/run/mysqld/mysqld.pid
#
Master-host = 192.168.204.139
Master-user = Tongbu
Master-pass = 123123
Master-port = 3306
Master-connect-retry = 60
#
Replicate-do-db=all

------------------------------------------

Restart MySQL Database

/etc/init.d/mysqld restart

If restarting MySQL fails, see if selinux shuts down ******

Then specify the master IP and the synchronized POS point from the server;
> Change Master to master_host= ' 192.168.204.139 ', master_user= ' Tongbu ', master_password= ' 123123 ', master_log_file= ' mysql-bin.000003 ', master_log_pos=415;
> Slave start;
> Show slave status\g;

# if slave_io_running:yes, Slave_sql_running:yes

==================================================

Vim Common command ******

# command-line mode

:%d #清空文件内容
:%s/wk/wkui/g #g Replace All
:%s/my/my/g #替换
:%s/my/&/g#my a space behind it.
:%s/^/&/g# start with a space
:%s/$/&/g# End With a space

==================================================

# # #另外配置 lamp,192.168.204.129

Yum install httpd httpd-devel php php-mysql php-devel mysql-server MySQL mysql-devel-y

# Turn off "firewall" and "SELinux"
Chkconfig--del iptables
/etc/init.d/iptables stop

Sed-i/selinux/s/enforcing/disabled/g/etc/selinux/config

# Default Web publishing directory/var/www/html

cd/root/
wget Http://download.comsenz.com/DiscuzX/3.2/Discuz_X3.2_SC_UTF8.zip

# Unzip the Discuz package:
Unzip discuz_x3.2_sc_utf8.zip-d/var/www/html/discuz
vim/etc/httpd/conf.d/discuz.conf # httpd reads files from CONF.D directory by default

Enter the following: If you are a httpd-2.4 version, also enter "Directory ..."

------------------------------------

<virtualhost *:80>
documentroot/var/www/discuz/upload/
</VirtualHost>

------------------------------------
Restart httpd

/ETC/INIT.D/HTTPD restart

# Access IP start installation discuz Forum

Ah ha, a lot of red xxx file permissions are not enough

Chmod-r 777/var/www/html/discuz/upload/# This is the figure of convenience, only 777 of the actual production must not be so, or the consequences of their own

In 192.168.204.139 "main" mysql New database * * * * *
> CREATE Database Discuz Charset=utf8;
> Grant all on discuz.* to [email protected] ' 192.168.204.129 ' identified by ' 123123 ';

Specify a database Discuz with 192.168.204.139 when installing Discuz, "map"

Install complete See "from" mysql:192.168.204.138, do you have a discuz database "map"

# Look at the configuration information of Discuz
/var/www/html/upload/config/config_global.php
/var/www/html/upload/config/config_ucenter.php

==================================================

Turn off "main" MySQL, visit website again, website still hang up

Although did the master and slave, but once the main MySQL hang, the site can not be visited, you can only Haha,

How can it not be affected, that is keepalived, high Availability , yeah

# keepalived + MySQL Master
Http://www.keepalived.org/software/keepalived-1.2.1.tar.gz

Tar XF keepalived-1.2.1.tar.gz
CD keepalived-1.2.1
./configure--with-kernel-dir=/usr/src/kernels/2.6.32-696.6.3.el6.x86_64/&& make && make install

# Make time error, yum install popt popt-devel-y

cp/usr/local/etc/rc.d/init.d/keepalived/etc/rc.d/init.d/
cp/usr/local/etc/sysconfig/keepalived/etc/sysconfig/

Mkdir-p/etc/keepalived
cp/usr/local/sbin/keepalived/usr/sbin/

# Modify the keepalived.conf configuration file on the "primary" server,
Vim/etc/keepalived/keepalived.conf

------------------------------------------
! Configuration File for Keepalived

Global_defs {
Notification_email {
[Email protected]
}

Notification_email_from [email protected]
Smtp_server 127.0.0.1
Smtp_connect_timeout 30
router_id Lvs_devel
}

# VIP1

Vrrp_instance Vi_1 {
State MASTER
Interface eth0
Lvs_sync_daemon_inteface eth0
VIRTUAL_ROUTER_ID 151
Priority 100
Advert_int 5
Nopreempt
Authentication {
Auth_type PASS
Auth_pass 2222
}

virtual_ipaddress {
192.168.204.150
}

}

Virtual_server 192.168.204.150 3306 {
Delay_loop 6
Lb_algo WRR
Lb_kind DR
Persistence_timeout 60
Protocol TCP

#
Real_server 192.168.204.139 3306 {
#
Weight 100
notify_down/data/sh/mysql.sh
Tcp_check {
Connect_timeout 10
Nb_get_retry 3
Delay_before_retry 3
Connect_port 3306

}
}
}

------------------------------------------

MySQL "from" Server Configuration keepalived.conf and master,
But three places need to be modified:
1, the Realserver IP is modified to real_server 192.168.204.138;
2, priority from 100 to 90;
3. Change level state MASTER to BACKUP

Create a/data/sh/mysql.sh script on the master, slave database, with the content:
/etc/init.d/keepalived stop

# The above command will stop the keepalived service after the MySQL service has stopped

Then restart the Keepalived service on both databases separately.

The final test stops the master MySQL service and will automatically switch to Backup.

MySQL optimizations can also be optimized for read-write separations, MYSQL+DRBD, split tables, and more. Go ahead and study it!

==================================================

Some questions ******

# Your machine may be missing the Ip_vs module
# Mount Ip_vs
Modprobe Ip_vs

Lsmod |grep Ip_vs

/etc/init.d/keepalived restart

# Remote copy of "main" MySQL keepalived configuration file
Scp-r keepalived.conf [Email protected]:/etc/keepalived/

Start Keepalived,

/etc/init.d/keepalived start

Check the machine IP address and you will find a new ip:192.168.204.150

For "main" MySQL database, add new user BBS

==================================================

keepalived + MySQL Master * * * * *

Remote 192.168.204.129, operate as follows:
# Redeploy Site Discuz, enter virtual IP 192.168.204.150

# After successful deployment, the new registration of two users

Logged in user

# stop "main" MySQL, refresh the site to see if it is normal???

MySQL database master-slave configuration