MySQL master-slave copy read and write separation--simple article

MySQL Basic topology diagram:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/72/B5/wKioL1Xr9GGS_ppoAAE_lZxWv1w826.jpg "title=" Mysql.png "alt=" Wkiol1xr9ggs_ppoaae_lzxwv1w826.jpg "/>

MySQL Environment preparation:

One MySQL master server (install MySQL)

Two MySQL slave servers (install MySQL)

One MySQL agent (install amoeba and Java)

A MySQL client (MySQL client)

Close all iptables,selinux before deployment

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/72/B6/wKioL1Xr-DXwUT65AAEThvZK0QU107.jpg "title=" 2015-09-06_161456.png "alt=" Wkiol1xr-dxwut65aaethvzk0qu107.jpg "/>

MySQL master-slave copy read/write detach required installation package:

Cmake-2.8.6.tar.gz

Mysql-5.5.22.tar.gz

Amoeba-mysql-binary-2.2.0.tar.gz

Jdk-7u65-linux-x64.tar.gz

Jdk-6u14-linux-x64.bin

Ncurses-devel

Deploy a time server for time synchronization:

Yum-y Install NTP

Edit the configuration file by adding the following line:

Server 127.127.1.0
Fudge 127.127.1.0 Stratum 8

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/72/B9/wKiom1Xr9PmidTetAAIjMt-xhlo436.jpg "title=" 2015-09-06_161006.png "alt=" Wkiom1xr9pmidtetaaijmt-xhlo436.jpg "/>

Restart Service:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/72/B6/wKioL1Xr93_hKC4pAABthFx3G88403.jpg "title=" 2015-09-06_161145.png "alt=" Wkiol1xr93_hkc4paabthfx3g88403.jpg "/>

Client Installation software synchronization:

Yum-y Install Ntp-date

/usr/sbin/ntpdate 99.99.99.22

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/72/B6/wKioL1Xr-NOy9bx5AACmi6jBW-I891.jpg "title=" 2015-09-06_161731.png "alt=" Wkiol1xr-noy9bx5aacmi6jbw-i891.jpg "/>

Install MySQL unique compile-and-install software CMake:

Tar zxf/root/cmake-2.8.6.tar.gz-c/root/
cd/root/cmake-2.8.6

./configure && gmake && gmake Install

To install the MySQL dependency package:

Yum-y Install Ncurses-devel

Compile and install MySQL

Tar zxf/root/mysql-5.5.22.tar.gz-c/usr/src
cd/usr/src/mysql-5.5.22

Cmake-dcmake_install_prefix=/usr/local/mysql \
-DSYSCONFDIR=/ETC \
-ddefault_charset=utf8 \
-DDEFAULT_COLLATION=UTF8_GENERAL_CI \
-dwith_extra_charsets=all
Make && make install

Authorized Database User:
Useradd-m-s/sbin/nologin mysql-g MySQL
Chown-r Mysql:mysql/usr/local/mysql

Initialize the database:

/usr/local/mysql/scripts/mysql_install_db \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data/

Optimizing the Database

cp-rf/usr/src/$MY _q/support-files/my-medium.cnf/etc/my.cnf

cp-rf/usr/src/$MY _q/support-files/mysql.server/etc/init.d/mysqld
chmod +x/etc/init.d/mysqld
Chkconfig--add mysqld

Chkconfig mysqld on

Start the database and set the password:

echo "path= $PATH:/usr/local/mysql/bin" >>/etc/profile

Source/etc/profile

/etc/init.d/mysqld start

Mysqladmin-uroot password ' 123123 '

Configuring the MySQL master server

Vim/etc/my.cnf

Server-id = one #改

Log-bin=master-bin #改

Log-slave-updates=true #添加

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/72/B6/wKioL1Xr_ETzMvlAAAE1xD8Dnss071.jpg "title=" 2015-09-06_163214.png "alt=" Wkiol1xr_etzmvlaaae1xd8dnss071.jpg "/>

Restart Service:

/etc/init.d/mysqld restart

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/72/BA/wKiom1Xr-mvCwWNLAACHm3Yqql0554.jpg "title=" 2015-09-06_163332.png "alt=" Wkiom1xr-mvcwwnlaachm3yqql0554.jpg "/>

Login to MySQL to authorize the server

[[email protected] ~]# mysql -uroot -p123123welcome to the mysql  Monitor.  commands end with ; or \g.your mysql connection id  is 4Server version: 5.5.22-log Source distributionCopyright  (c)  2000,  2011, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql>grant replication slave on *.* to  ' myslave ' @ ' 99.99.99.% '  identified by  ' 123123 '; query ok, 0 rows affected  (0.00 sec) MYSQL>&NBsp;flush privileges; query ok, 0 rows affected  (0.00 sec) mysql> show master status  ;+-------------------+----------+--------------+------------------+| file               | position | binlog_do_db |  binlog_ignore_db |+-------------------+----------+--------------+------------------+|  master-bin.000002 |      336 |               |                   |+-------------------+----------+--------------+-------------- ----+1 row in set  (0.00 SEC)

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/72/B7/wKioL1Xr_wXyre1PAAPdCtfVTfA908.jpg "title=" 2015-09-06_164401.png "alt=" Wkiol1xr_wxyre1paapdctfvtfa908.jpg "/>

The database from the server is consistent with the primary server and no commands need to be configured.

Configure the first one to add the following line from the server:

Vim/etc/my.cnf
Server-id = #改
Relay-log=relay-log-bin #添加
Relay-log-index=slave-relay-bin.index #添加

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/72/BA/wKiom1Xr_eqjQawjAAFCV-bBNC4636.jpg "title=" 2015-09-06_164650.png "alt=" Wkiom1xr_eqjqawjaafcv-bbnc4636.jpg "/>

Restart Service:

Service mysqld Restart

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/72/B7/wKioL1XsAJTDdzYYAAB-sYSfvQM129.jpg "title=" 2015-09-06_165021.png "alt=" Wkiol1xsajtddzyyaab-sysfvqm129.jpg "/>

Configuration database:

[[email protected] ~]# mysql -uroot -p123123welcome to the mysql  Monitor.  commands end with ; or \g.your mysql connection id  is 4Server version: 5.5.22-log Source distributionCopyright  (c)  2000,  2011, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> change master to master_host= ' 99.99.99.22 ', master_user= ' Myslave ' , master_password= ' 123123 ', master_log_file= ' master-bin.000001 ',master_log_pos=336;start slave;show  slave  Status\g;             slave_io_running:  yes            slave_sql_running: yes

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/72/B7/wKioL1XsAabh6P3CAAPkuI6IoRA303.jpg "title=" 2015-09-06_165507.png "alt=" Wkiol1xsaabh6p3caapkui6iora303.jpg "/>

Configure the second one to add the following line from the server:

Vim/etc/my.cnf

Server-id = #改
Relay-log=relay-log-bin #添加
Relay-log-index=slave-relay-bin.index #添加

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/72/BC/wKiom1XsEinSpu5hAAHzPIrM7KU348.jpg "title=" 2015-09-06_181449.png "alt=" Wkiom1xseinspu5haahzpirm7ku348.jpg "/>

Restart Service:

/etc/init.d/mysqld restart

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/72/B8/wKioL1XsFPODqVWqAACA6eR8TfM792.jpg "title=" 2015-09-06_181733.png "alt=" Wkiol1xsfpodqvwqaaca6er8tfm792.jpg "/>

Configure MySQL database:

[[email protected] ~]# mysql -uroot -p123123welcome to the mysql  Monitor.  commands end with ; or \g.your mysql connection id  is 4Server version: 5.5.22-log Source distributionCopyright  (c)  2000,  2011, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> change master to master_host= ' 99.99.99.22 ', master_user= ' Myslave ' , master_password= ' 123123 ', master_log_file= ' master-bin.000001 ',master_log_pos=336;start slave;show  slave  Status\g;             slave_io_running:  yes            slave_sql_running: yes

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/72/B8/wKioL1XsFB-yoLqwAAM3DggNPGI909.jpg "title=" 2015-09-06_181358.png "alt=" Wkiol1xsfb-yolqwaam3dggnpgi909.jpg "/>

Verifying the effect of master-slave replication

Log in to the master server and create the database:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/72/BC/wKiom1XsEyfTDYaeAAELoTFk-y0710.jpg "title=" 2015-09-06_181904.png "alt=" Wkiom1xseyftdyaeaaelotfk-y0710.jpg "/>

View from server:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/72/BC/wKiom1XsE1SCslGdAADHUqiEFTI651.jpg "title=" 2015-09-06_181949.png "alt=" Wkiom1xse1scslgdaadhuqiefti651.jpg "/>

To achieve MySQL read-write separation (to be continued):

This article is from the "XXX" blog, please be sure to keep this source http://moumou.blog.51cto.com/9995443/1691998

MySQL master-slave copy read and write separation--simple article