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

Description:

Operating system: CentOS 5.x 64 bit

MySQL database version: mysql-5.5.35

MySQL master server: 192.168.21.128

MySQL slave server: 192.168.21.129

Preparation:

Description: do the following on two MySQL servers 192.168.21.128 and 192.168.21.129, respectively

Note:

It is recommended to use the same version of MySQL as the master and slave server!

Or you must make sure that the MySQL version of the master server is higher than the MySQL version of the slave server!

First, configure IP, DNS and gateway to ensure that you can connect to the server using remote connection tools.

Configure the firewall and open port 3306

Vi / etc/sysconfig/iptables # editing

-A RH-Firewall-1-INPUT-m state-- state NEW-m tcp-p tcp-- dport 3306-j ACCEPT # allow port 3306

Special note: if these two rules are added to the last line of the firewall configuration, causing the firewall to fail to start, the correct one should be added to the default port 22 rule below. After adding, the firewall rules are as follows:

# # #

# Firewall configuration written by system-config-securitylevel

# Manual customization of this file is not recommended.

* filter

: INPUT ACCEPT [0:0]

: FORWARD ACCEPT [0:0]

: OUTPUT ACCEPT [0:0]

: RH-Firewall-1-INPUT-[0:0]

-An INPUT-j RH-Firewall-1-INPUT

-A FORWARD-j RH-Firewall-1-INPUT

-A RH-Firewall-1-INPUT-I lo-j ACCEPT

-A RH-Firewall-1-INPUT-p icmp--icmp-type any-j ACCEPT

-A RH-Firewall-1-INPUT-p 50-j ACCEPT

-A RH-Firewall-1-INPUT-p 51-j ACCEPT

-A RH-Firewall-1-INPUT-p udp-- dport 5353-d 224.0.0.251-j ACCEPT

-A RH-Firewall-1-INPUT-p udp-m udp-- dport 631-j ACCEPT

-A RH-Firewall-1-INPUT-p tcp-m tcp-- dport 631-j ACCEPT

-A RH-Firewall-1-INPUT-m state-- state ESTABLISHED,RELATED-j ACCEPT

-A RH-Firewall-1-INPUT-m state-- state NEW-m tcp-p tcp-- dport 22-j ACCEPT

-A RH-Firewall-1-INPUT-m state-- state NEW-m tcp-p tcp-- dport 3306-j ACCEPT

-A RH-Firewall-1-INPUT-j REJECT-- reject-with icmp-host-prohibited

COMMIT

# # #

/ etc/init.d/iptables restart # finally restart the firewall for the configuration to take effect

3. Close SELINUX

Vi / etc/selinux/config

# SELINUX=enforcing # comment out

# SELINUXTYPE=targeted # comment out

SELINUX=disabled # increased

: wq # Save exit

Setenforce 0 # effective immediately

IV. System agreement

Software source code package location: / usr/local/src

Source package compilation installation location: / usr/local/ software name

Download the software package

1. Download MySQL

Http://cdn.mysql.com/Downloads/MySQL-5.5/mysql-5.5.35.tar.gz

2. Download cmake (MySQL compilation tool)

Http://www.cmake.org/files/v2.8/cmake-2.8.12.1.tar.gz

The above packages are uploaded to the / usr/local/src directory

6. Install compilation tools and library files (install using CentOS yum command)

Yum install-y make apr* autoconf automake curl curl-devel gcc gcc-c++ gtk+-devel zlib-devel openssl openssl-devel pcre-devel gd kernel keyutils patch perl kernel-headers compat* cpp glibc libgomp libstdc++-devel keyutils-libs-devel libsepol-devel libselinux-devel krb5-devel libXpm* freetype freetype-devel freetype* fontconfig fontconfig-devel libjpeg* libpng* php-common php-gd gettext gettext-devel ncurses* libtool* libxml2 libxml2-devel patch policycoreutils bison

Installation section

Description: install mysql-5.5.35 on two MySQL servers 192.168.21.128 and 192.168.21.129, respectively

The following is a remote login to the server, operating under the command line

First, install cmake

Cd / usr/local/src

Tar zxvf cmake-2.8.12.1.tar.gz

Cd cmake-2.8.12.1

. / configure

Make # compilation

Make install # installation

Second, install mysql

Groupadd mysql # add mysql Group

Useradd-g mysql mysql-s / bin/false # create a user mysql and join the mysql group, and do not allow mysql users to log in directly to the system

Mkdir-p / data/mysql # create MySQL database storage directory

Chown-R mysql:mysql / data/mysql # set MySQL database directory permissions

Mkdir-p / usr/local/mysql-5.5.35 # create the MySQL installation directory

Ln-s / usr/local/mysql-5.5.35 / usr/local/mysql # create a soft connection to the mysql directory for later configuration

Cd / usr/local/src

Tar zxvf mysql-5.5.35.tar.gz # decompression

Cd mysql-5.5.35

Cmake. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-DMYSQL_DATADIR=/data/mysql-DSYSCONFDIR=/etc # configuration

Make # compilation

Make install # installation

Cd / usr/local/mysql

Cp. / support-files/my-huge.cnf / etc/my.cnf # copy the configuration file (Note: if there is a my.cnf by default under the / etc directory, you can overwrite it directly)

Vi / etc/my.cnf # edit the configuration file and add it in the [mysqld] section

Datadir = / data/mysql # add MySQL database path

. / scripts/mysql_install_db-- user=mysql # generates mysql system database

Cp. / support-files/mysql.server / etc/rc.d/init.d/mysqld # add Mysql to the system startup

Chmod 755 / etc/init.d/mysqld # increased execution permissions

Chkconfig mysqld on # join Boot Boot

Vi / etc/rc.d/init.d/mysqld # editing

Basedir = / usr/local/mysql # MySQL program installation path

Datadir = / data/mysql # MySQl database storage directory

Service mysqld start # Startup

Vi / etc/profile # adds the mysql service to the system environment variable: add the following line at the end

Export PATH=$PATH:/usr/local/mysql/bin

Source / etc/profile # makes the configuration effective immediately

The following two lines link the library files of myslq to the default location of the system, so that you don't have to specify the library file address of mysql when compiling software like PHP.

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

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

Mkdir / var/lib/mysql # create directory

Ln-s / tmp/mysql.sock / var/lib/mysql/mysql.sock # add soft links

Mysql_secure_installation # set the Mysql password and press enter Y to enter the password twice as prompted

/ usr/local/mysql/bin/mysqladmin-u root-p password "123456" # or change the password directly

At this point, the mysql installation is complete!

Configuration section

Configure the MySQL master server (192.168.21.128)

Mysql-u root-p # enter the MySQL console

Create database osyunweidb; # Building a database osyunweidb

Insert into mysql.user (Host,User,Password) values ('localhost','osyunweiuser',password (' 123456')); # create user osyunweiuser with password 123456

Grant all on osyunweidb.* to 'osyunweiuser'@'192.168.21.130' identified by' 123456' with grant option; # authorizes user osyunweiuser to fully access the database from 192.168.21.130. Note: 192.168.21.130 here is to connect to the database Web server IP

Insert into mysql.user (Host,User,Password) values ('localhost','osyunweidbbak',password (' 123456')); # establish MySQL master-slave database synchronization user osyunweidbbak password 123456

Flush privileges; # refresh the system authorization table

Grant replication slave on *. * to 'osyunweidbbak'@'192.168.21.129' identified by' 123456' with grant option; # authorized user osyunweidbbak can only access the database on the main server 192.168.21.128 from the IP 192.168.21.129, and only has the right to back up the database

Import the database osyunweidb from the MySQL master server 192.168.21.128 into the MySQL slave server 192.168.21.129

1. Export database osyunweidb

Mysqldump-u root-p-- default-character-set=utf8-- opt-Q-R-- skip-lock-tables osyunweidb > / home/osyunweidbbak.sql # operates on the MySQL master server and exports the database osyunweidb to / home/osyunweidbbak.sql

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

Flush tables with read lock; # database read-only lock command to prevent data writing when exporting the database

Unlock tables; # unlock

Scp / home/osyunweidbbak.sql root@192.168.21.129:/home # upload the osyunweidbbak.sql database file from the home directory to the MySQL slave server's home directory

System operation and maintenance www.osyunwei.com warm reminder: the original content of qihang01 all rights reserved, reprint please indicate the source and the original link

2. Import database to MySQL slave server

Mysql-u root-p # enter the slave server MySQL console

Create database osyunweidb; # create a database

Use osyunweidb # enter the database

Source / home/osyunweidbbak.sql # Import backup files to the database

Mysql-u osyunweidbbak-h 192.168.21.128-p # test logs in to the master server on the slave server

Configure the my.cnf file of the MySQL master server (192.168.21.128)

Vi / etc/my.cnf # edit the configuration file and add the following in the [mysqld] section

Server-id=1 # sets the server id, and a value of 1 means the primary server. Note: if you already have this line in the original configuration file, you don't need to add it any more.

Log-bin=mysql-bin # starts the MySQ binary logging system. Note: if you already have this line in the original configuration file, you don't need to add it any more.

Binlog-do-db=osyunweidb # requires a synchronized database name. If you have more than one database, you can repeat this parameter, one row for each database

Binlog-ignore-db=mysql # does not synchronize the mysql system database

: wq! # Save exit

Service mysqld restart # restart MySQL

Mysql-u root-p # enter the mysql console

Show variables like 'server_id'; # check to see if the value of server-id is 1

Mysql > show variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 1 | |

+-+ +

1 row in set (0.00 sec)

Show master status; # looks at the master server and the following similar message appears

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000011 | 107 | osyunweidb | mysql | |

+-+

1 row in set (0.00 sec)

Note: remember the value of File here: the value of mysql-bin.000011 and Position: 107, which will be used later.

Configure the my.cnf file of the MySQL slave server (192.168.21.129)

Vi / etc/my.cnf # edit the configuration file and add the following in the [mysqld] section

Server-id=2 # sets the server id and modifies its value to 2, which is represented as a slave database

Log-bin=mysql-bin # starts the MySQ binary logging system. Note: if you already have this line in the original configuration file, you don't need to add it any more.

Replicate-do-db=osyunweidb # requires a synchronized database name. If you have more than one database, you can repeat this parameter, one row for each database

Replicate-ignore-db=mysql # does not synchronize the mysql system database

Read_only # make the database read-only

: wq! # Save exit

Service mysqld restart # restart MySQL

Mysql-u root-p # enter the MySQL console

Show variables like 'server_id'; # to view the value of server-id, it must be 2 set above, otherwise please return to modify the configuration file

Mysql > show variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 2 | |

+-+ +

1 row in set (0.01 sec)

Slave stop; # stop the slave synchronization process

Change master to master_host='192.168.21.128',master_user='osyunweidbbak',master_password='123456',master_log_file='mysql-bin.000011', master_log_pos=107; # executes synchronization statements

Slave start; # start the slave synchronization process

SHOW SLAVE STATUS\ G # looks at the slave synchronization information and the following appears

Mysql > SHOW SLAVE STATUS\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.21.128

Master_User: osyunweidbbak

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000011

Read_Master_Log_Pos: 107

Relay_Log_File: mysqlslave-relay-bin.000004

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000011

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: osyunweidb

Replicate_Ignore_DB: mysql

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 107

Relay_Log_Space: 560

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)

Mysql >

Note to view:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

The values of the above two parameters are Yes, which means the configuration is successful!

Test piece

Test whether the MySQL master-slave server is running properly

1. Enter the MySQL master server (192.168.21.128)

Mysql-u root-p # enter the MySQL console

Use osyunweidb # enter the database

CREATE TABLE test (id int not null primary key,name char (20)); # create test table

2. Enter MySQL from the server

Mysql-u root-p # enter the MySQL console

Use osyunweidb # enter the database

Show tables; # looking at the osyunweidb table structure, you will see a new table test, indicating that the database is synchronized successfully

Mysql > show tables

+-+

| | Tables_in_osyunweidb |

+-+

| | test |

+-+

1 row in set (0.00 sec)

At this point, the master-slave synchronization configuration of MySQL database under Linux is complete!

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.