MySQL Master-Detail installation steps

Website:

Program on:Web server 192.168.1.100 above

database in: MySQL server 192.168.1.123 above

Implementation Purpose: add a MySQL backup server (192.168.1.124), as a MySQL server (192.168.1.123) from the server, the two MySQL servers to achieve dual-machine hot standby. That is, the data specified above the server (192.168.1.124) is always synchronized with the specified data above the primary server (192.168.1.123) and varies with the specified database above the primary server (192.168.1.123).

Environment description

1. Web server

System: CentOS 6.5-x64

ip:192.168.21.129

Web environment: apache+php (None)

2. mysql master server

System: CentOS 6.5-x64

ip:192.168.1.123

Host Name: Mysqlmaster

MySQL version: mysql-5.5.22

3. mysql slave server

System: CentOS 6.5-x64

ip:192.168.1.124

Host Name: Mysqlslave

MySQL version: mysql-5.5.22

4. Client

System: Windows 7

ip:192.168.21.130

Note: MySQL as a master-slave server The version is recommended to use the same version! Or you must ensure that the MySQL version of the master server is higher than the MySQL version from the server (the MySQL version is backwards compatible)

_______________________________________________________________________________

Tutorial Start:

First, install MySQL

Description: Install MySQL 5.5.22 on two MySQL servers 192.168.1.123 and 192.168.1.124, respectively

1, configure the firewall, open the MySQL default 3306 port

[Email protected] ~]# Vi/etc/sysconfig/iptables

#编辑防火墙配置文件:-A input-m State--state new-m tcp-p TCP--dport 3306-j ACCEPT

[Email protected] ~]#/etc/init.d/iptables restart

#重启防火墙 for the configuration to take effect

2. Turn off SELinux

[Email protected] ~]# Vi/etc/selinux/config

#SELINUX =enforcing #注释掉

#SELINUXTYPE =targeted #注释掉

Selinux=disabled #修改, there was no increase in

: Wq #保存, close

[Email protected] ~]# Shutdown-r now #重启系统

3. Install the Compilation tool

[email protected] ~]# yum install wget make apr* autoconf automake curl-devel gcc gcc-c++ zlib-devel OpenSSL openssl-deve L Pcre-devel GD kernel keyutils patch perl kernel-headers compat* MPFR cpp glibc libgomp libstdc++-devel ppl cloog-ppl Keyutils-libs-devel libcom_err-devel libsepol-devel libselinux-devel krb5-devel zlib-devel libXpm* freetype libjpeg* libpng* Php-common php-gd ncurses* libtool* libxml2 libxml2-devel patch

4. Download the package

CD/USR/LOCAL/SRC #进入软件包下载目录

(1), download CMake (MySQL compilation tool)

Wgethttp://www.cmake.org/files/v2.8/cmake-2.8.7.tar.gz

(2), download MySQL

Wgethttp://mysql.mirror.kangaroot.net/downloads/mysql-5.5/mysql-5.5.22.tar.gz

Note: MySQL5.5 version will need to use CMake to compile the installation, can be downloaded to the computer with Lrzsz upload

[email protected] src]# Yum install-y lrzsz

[Email protected] src]# RZ

5, installation CMake

[Email protected] src]# tar zxvf cmake-2.8.7.tar.gz

[Email protected] src]# CD cmake-2.8.7

[Email protected] cmake-2.8.7]#./bootstrap

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

The first time the installation error is as follows:

Error: C compiler is missing

[[email protected] ~]# Yum install gcc

Continue with CMake installation

[Email protected] cmake-2.8.7]#./bootstrap

Error again: missing C + + compiler

[email protected] ~]# Yum install gcc-c++

Repeat the above action

[Email protected] cmake-2.8.7]#./bootstrap

[[email protected] cmake-2.8.7]# make && make install #编译并安装

Note: Check the installation which cmake see the results of the installation is correct

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

6. Install MySQL

[[email protected] ~]# groupadd mysql# add MySQL Group

[Email protected] ~]# useradd-g MySQL mysql-s/bin/false

#创建用户mysql并加入到mysql组, MySQL users are not allowed to log in directly to the system

Note: [[email protected] ~]# groups MySQL view the group where the MySQL user is located, as well as the members within the group

[[email protected] ~]# mkdir-p/data/mysql# create MySQL database storage directory

[[email protected] ~]# chown mysql:mysql/data/mysql–r# set MySQL database directory permissions

[[email protected] ~]# mkdir-p/usr/local/mysql# create MySQL installation directory

[Email protected] ~]# cd/usr/local/src/

[[Email protected] src]# tar zxvf mysql-5.5.22.tar.gz# unzip MySQL

[Email protected] src]# CD mysql-5.5.22

[Email protected] mysql-5.5.22]# cmake. \

>-dcmake_install_prefix=/usr/local/mysql \

>-dmysql_datadir=/data/mysql \

>-DSYSCONFDIR=/ETC \ #配置

Error: Missing Ncurses-devel package

[[Email protected] ~] #yum Install Ncurses-devel

Delete CMakeCache.txt file

[Email protected] ~]# Find/-name CMakeCache.txt

[[email protected] ~]# rm–f+ Absolute path #删除查找到的文件即可

Repeat the above action

[Email protected] mysql-5.5.22]# cmake. \

>-dcmake_install_prefix=/usr/local/mysql \

>-dmysql_datadir=/data/mysql \

>-DSYSCONFDIR=/ETC \ #配置

[[email protected] mysql-5.5.22]# make && make install# compile and install

[Email protected] ~]# Cd/usr/local/mysql

[email protected] mysql]# CP./support-files/my-huge.cnf/etc/my.cnf

#拷贝配置文件 (Note: If the/etc directory has a default MY.CNF, you can overwrite it directly)

[Email protected] mysql]# VI/ETC/MY.CNF

#编辑配置文件, increase in [mysqld] section

DataDir =/data/mysql #添加MySQL数据库路径 (note = left and right spaces)

[Email protected] mysql]#/scripts/mysql_install_db--user=mysql

#生成mysql系统数据库

[email protected] mysql]# CP./support-files/mysql.server/etc/rc.d/init.d/mysqld

#把Mysql加入系统启动

[Email protected] mysql]# chmod 755/etc/init.d/mysqld #增加执行权限

[Email protected] mysql]# chkconfig mysqld on #加入开机启动

[Email protected] mysql]# Vi/etc/rc.d/init.d/mysqld

#编辑: Basedir =/usr/local/mysql #MySQL程序安装路径

DataDir =/data/mysql #MySQl数据库存放目录

[Email protected] mysql]# service mysqld start #启动MySQL

[Email protected] mysql]# Vi/etc/profile

#把mysql服务加入系统环境变量

Add the following line at the end: Export path= $PATH:/usr/local/mysql/bin

The following two lines link the MYSLQ library file to the default location of the system, so you can compile software like PHP without specifying the MySQL library file address

Ln-s/usr/local/mysql/lib/mysql/usr/lib/mysql

Ln-s/usr/local/mysql/include/mysql/usr/include/mysql

[email protected] mysql]# reboot

#需要重启系统, wait for the system to restart after the operation continues under the terminal command line

Note: or use Shutdown–r now to restart the system

[[email protected] ~]# mysql_secure_installation # mysql Security Configuration Wizard, set MySQL password

[Email protected] ~]# mysql_secure_installation

Note:running all PARTS of this SCRIPT are RECOMMENDED for all MySQL

SERVERS in PRODUCTION use! Please READ each STEP carefully!

In order to log into MySQL to secure it, we'll need the current

Password for the root user. If you ' ve just installed MySQL, and

You haven ' t set the root password yet, the password would be blank,

So, should just press ENTER here.

Enter current password to root (enter for none): <-First run direct carriage return

OK, successfully used password, moving on ...

Setting The root password ensures that nobody can log into the MySQL

Root user without the proper authorisation.

Set root Password? [y/n] y<-whether to set the root user password, enter Y and return or directly enter

New Password: <-set root user password

Re-enter new password: <-Enter the password you set again

Password Updated successfully!

Reloading privilege tables.

... success!

By default, a MySQL installation have an anonymous user, allowing anyone

To log into MySQL without has to has a user account created for

them. This was intended only for testing, and the installation

Go a bit smoother. You should remove them before moving into a

Production environment.

Remove anonymous users? [y/n] y<-whether to delete anonymous users, production environment proposed to delete

... success!

Normally, Root should only is allowed to connect from ' localhost '. This

Ensures that someone cannot guess at the root of password from the network.

Disallow Root login remotely? [y/n] y<-time to prohibit the root remote login, according to their own needs to choose y/n and enter, it is recommended to prohibit

... success!

By default, MySQL comes with a database named ' test ' that anyone can

Access. This was also intended only for testing, and should be removed

Before moving into a production environment.

Remove test database and access to it? [y/n] y<-whether to delete the test database and access to the test library, enter directly

-Dropping test database ...

... success!

-Removing privileges on test database ...

... success!

Reloading the privilege tables would ensure that all changes made so far

would take effect immediately.

Reload privilege tables now? [y/n] <-whether to reload the permissions table, enter directly

... success!

Cleaning up ...

All done! If you've completed all of the above steps, your MySQL

Installation should now is secure.

Thanks for using mysql!

or change the password directly

[Email protected] ~]#/usr/local/mysql/bin/mysqladmin–u root–p password ' 123456 '

[[Email protected] ~] #service mysql restart #重启MySQL服务

To this MySQL installation is complete!

Second, do the following in the Web server 192.168.21.129

Upload the Web application to the appropriate directory of the Web server 192.168.21.129 (my Apache default site Directory here is/var/www/html)

Log on to the Web server and execute the following command

Chownapache.apache/var/www/html-r #设置网站目录权限

Third, configure MySQL master server (192.168.1.123)

[[email protected] ~]# mysql-u root-p# enter MySQL console

mysql> CREATE database Yunweia; #建立数据库yunweia

mysql> INSERT INTO Mysql.user (Host,user,password)

VALUES (' localhost ', ' yunweiuser ', password (' 123456 ')); #创建用户yunweiuser

Mysql> Grant all on yunweia.* to ' yunweiuser ' @ ' 192.168.21.129 ' identified by ' 123456 ' with GRANT option;

#授权用户 (Yunweiuser) full access to the database from the Web server 192.168.21.129 (Yunweia)

mysql> INSERT INTO Mysql.user (Host,user,password), values (' localhost ', ' Yunweib ', Password (' 123456 ') );

#建立MySQL主从数据库同步用户yunweidb密码123456

mysql> flush Privileges; #刷新系统授权表

mysql> grant replication Slave on * * to ' Yunweib ' @ ' 192.168.1.122 ' identified by ' 123456 ' with GRANT option;

#授权用户yunweib只能从192.168.1.122 This IP accesses the primary server 192.168.1.123 the database above and has only the database backup permissions.

Iv. in client Windows 7 (192.168.21.130), do the following

In the client Windows 7 (192.168.21.130) browser, open http://bbs.osyunwei.com/ (bbs.osyunwei.com domain name to be resolved to the Web server 192.168.21.129), appears the following program installation interface, start installation-Agree agreement, next:

Database server: 192.168.1.123

Database user name: Yunweiuser

Database Password: 123456

Database name: Yunweia

Skip the back!

V. Import the database Yunweia from MySQL master server 192.168.1.1239 to MySQL from the server 192.168.1.122

1. Export Database Yunweia

mysql> show databases; #查看所有库

Note: Before exporting, you can go to the MySQL console and execute the following command

Mysql> flush tables with read lock;

#数据库只读锁定命令 to prevent data from being written when the database is exported

mysql> unlock tables; #解除锁定

[Email protected] ~]# mysqldump-u root-p yunweia>/home/yunweib.sql

#在MySQL主服务器进行操作, export database Yunweia to/home/yunweidb.sql

[Email protected] ~]# scp/home/yunweib.sql [email protected]:/home/

#把home目录下的yunweidb. sql database files are uploaded to MySQL from the server's home directory below

Note: Scp–r folder user @ip: Path #可以传输文件夹

2. Import database to MySQL from server

[[email protected] ~]# mysql-u root–p# Enter MySQL console from server

mysql> CREATE database Yunweia; #创建数据库yunweia

Mysql> use yunweia# into the database

Mysql> Source/home/yunweib.sql #导入备份文件到数据库

Mysql-u osyunweidbbak-h 192.168.21.169-p #测试在从服务器上登录到主服务器

Note: You can use the following command on the primary server (192.168.1.123) to query all users and permissions in the MySQL database (two commands are available)

Mysql> select User,host,password from Mysql.user;

Mysql> SELECT DISTINCT CONCAT (' User: ', user, ' @ ', host, '; ') as query from Mysql.user;

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

Extended Knowledge:

mysql> show databases; #查看所有表

Mysql> select * from Mysql.user\g; #查看所有用户权限

Add user

Grant all on the database name. * To user name @localhost identified by ' password ';

Grant all on gamesp.* to [email protected] identified by ' password ';

Add a remote user named username password of password

GRANT all privileges on * * to [e-mail protected] "%" identified by ' password '

Description

(1) Grant all grants all permissions

(2) All tables in the gamesp.* database Gamesp

(3) NewUser user name

(4) @localhost the MySQL server server on the local computer

(5) Identfified by ' Password ' Set password

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

Vi. Configuring the MySQL master server my.cnf file

[Email protected] ~]# VI/ETC/MY.CNF

#编辑配置文件, add the following in the [Mysqld] section:

Server-id=1

#设置服务器id, represents the primary server for 1, note: If you already have this line in the original configuration file, you don't have to add it anymore.

Log-bin=mysql-bin

#启动MySQ二进制日志系统, Note: If you already have this line in your original configuration file, you don't need to add

Binlog-do-db=osyunweidb

#需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database

binlog-ignore-db=mysql# MySQL system database of different steps

: wq! #保存退出 (or uppercase ZZ)

[[Email protected] ~]# service mysqld restart# restart MySQL

[[email protected] ~]# mysql-u root–p# enter MySQL console

Mysql> Show master status; View the primary server with the following similar information

Note: Remember here that file value for: mysql-bin.000009 and Position value for: 107 , which will be used later.

Vii. configuring MySQL my.cnf files from the server

[Email protected] ~]# VI/ETC/MY.CNF

#编辑配置文件, add the following in the [Mysqld] section:

Server-id = 2

#配置文件中已经有一行server-id=1, modifying its value to 2, expressed as a slave from the database

Log-bin=mysql-bin

#启动MySQ二进制日志系统, Note: If you already have this line in your original configuration file, you don't need to add

Replicate-do-db=osyunweidb

#需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database

Replicate-ignore-db=mysql #不同步mysql系统数据库

: wq! #保存退出 (or uppercase ZZ)

[Email protected] ~]# service mysqld Restart #重启MySQL

Note: MySQL 5.1.7 version, the master is not supported Configuration Property Write My.cnf configuration file, just write the synchronized database and the database you want to ignore.

[[email protected] ~]# mysql-u root–p# enter MySQL console

mysql> slave stop; #停止slave同步进程

Mysql> Change Master to

-master_host= ' 192.168.1.123 ', master_user= ' yunweidb ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000009 ', master_log_pos=107; #执行同步语句

mysql> slave start; #开启slave同步进程

Mysql> show slave status\g; #查看slave同步信息, the following appears

Note check:

Slave_io_running:yes

Slave_sql_running:yes

The values for these two parameters are yes, which means the configuration was successful!

Eight, test MySQL master server dual-Machine hot standby is successful

1. Go to MySQL master server

[[email protected] ~]# mysql-u root–p# enter MySQL console

Mysql> use yunweia# into the database

mysql> CREATE TABLE Test (ID int not NULL primary key,name char); #创建test表

2. Enter MySQL from the server

[[email protected] ~]# mysql-u root–p# enter MySQL console

Mysql> use yunweia# into the database

Mysql> Show tables;

#查看yunweia表结构, you see a new table test that indicates that the database synchronization was successful.

At this point, MySQL database configuration master-slave server to implement a two-machine hot spare instance tutorial completed!

MySQL Master-Detail installation steps