mysql-database-Multi-machine configuration

One master multi-slave configuration

Environment:

Host A (HUANGZP2): 172.16.115.157

Host B (HUANGZP3): 172.16.115.100

Host C (HUANGZP4): 172.16.115.87

Description: Multiple slave servers provide backup function for one master server, still master-slave configuration, at least 3 machines required

One. Install MySQL mysql-server on 3 servers

Yum install MySQL Mysql-server-y

Two. Modify the configuration file/etc/my.cnf and restart

On Host A (master):

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/8D/7E/wKioL1iezY-RHHE4AAAhBZTEbGs804.png "title=" 21. PNG "alt=" Wkiol1iezy-rhhe4aaahbztebgs804.png "/>

On Host B (slave):

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/8D/7E/wKioL1iezaPytoWnAAAf3NwiR6M868.png "title=" 22. PNG "alt=" Wkiol1iezapytownaaaf3nwir6m868.png "/>

On Host C (slave):

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/8D/81/wKiom1ieza_QZGONAAAgFvyJVG4932.png "title=" 23. PNG "alt=" Wkiom1ieza_qzgonaaagfvyjvg4932.png "/>

Three. On primary server A, grant replication to multiple slave servers (Host B and host C) slave

Grant Replication Slave on * * to ' silen ' @ ' 172.16.115.100 ' identified by ' 123456 ';

Grant Replication Slave on * * to ' silen ' @ ' 172.16.115.87 ' identified by ' 123456 ';

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/8D/81/wKiom1iezb_R5rRTAAAiCU4alck645.png "title=" 31. PNG "alt=" Wkiom1iezb_r5rrtaaaicu4alck645.png "/>

Show master status;

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/8D/7E/wKioL1iezcqw8xtHAAAVqkgnL8o990.png "title=" 32. PNG "alt=" Wkiol1iezcqw8xthaaavqkgnl8o990.png "/>

Four. Write Master.info information from the top, and turn on slave, and view slave status

on Host B (slave):

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/8D/7E/wKioL1iezdjBvbDRAAAXK7F8I4o648.png "style=" float : none; "title=" 41.png "alt=" Wkiol1iezdjbvbdraaaxk7f8i4o648.png "/>

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/8D/81/wKiom1iezdnhONq_AABAIm2_SMo858.png "style=" float : none; "title=" 411.png "alt=" Wkiom1iezdnhonq_aabaim2_smo858.png "/>

On Host C (slave):

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/8D/7E/wKioL1iezfTTqTqbAAAXBEWaSWc757.png "style=" float : none; "title=" 42.png "alt=" Wkiol1iezfttqtqbaaaxbewaswc757.png "/>

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/8D/81/wKiom1iezfSDrOOjAAA-_p0ozrA181.png "style=" float : none; "title=" 422.png "alt=" Wkiom1iezfsdroojaaa-_p0ozra181.png "/>

Five. Create a AA on the primary server and view the DB1 library created synchronously from the server

Master A:

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/8D/81/wKiom1iezguibiWcAAAaoj6wkko964.png "title=" 51. PNG "alt=" Wkiom1iezguibiwcaaaaoj6wkko964.png "/>

From B:

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/8D/7F/wKioL1iezhrweyO4AAAQX08tEik467.png "title=" 52. PNG "alt=" Wkiol1iezhrweyo4aaaqx08teik467.png "/>

From C:

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/8D/7F/wKioL1ieziWAbhf-AAAQT3Wr6KY155.png "title=" 53. PNG "alt=" Wkiol1ieziwabhf-aaaqt3wr6ky155.png "/>

Multi-master one from configuration

Environment:

Host A (HUANGZP2): 172.16.115.157

Host B (HUANGZP3): 172.16.115.100

Host C (HUANGZP4): 172.16.115.87

Note: Whether it is master-slave, master, and always from, if the master has more than one database, you need to have more from the server to back up, the resource overhead is relatively large; a slave server for multiple primary servers to provide backup, there is a problem, is a slave server can not open two MySQL process (mysqld_ Safe can only open a database process), solve: Can not use mysqld_safe, but need to use Mysqld_multi, a server to provide multiple MySQL process

I. 3 machines yum install-y MySQL mysql-server and modifies the configuration file/etc/my.cnf

Master one A:

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/8D/81/wKiom1ie013iPSE2AAAg3i7F2yk023.png "title=" 11. PNG "alt=" Wkiom1ie013ipse2aaag3i7f2yk023.png "/>

Main two B:

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/8D/81/wKiom1ie02ni-BY2AAAfXqPFFZk691.png "title=" 12. PNG "alt=" Wkiom1ie02ni-by2aaafxqpffzk691.png "/>

Two. Authorization to the Slave server

Master one A:

Grant Replication Slave on * * to ' silen ' @ ' 172.16.115.87 ' identified by ' 123456 ';

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/8D/7F/wKioL1ie03WQ-LJLAAAnAowVWoU982.png "title=" 21. PNG "alt=" Wkiol1ie03wq-ljlaaanaowvwou982.png "/>

Main two B:

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/8D/81/wKiom1ie04XSqrb3AAAkLtbNbFI369.png "title=" 22. PNG "alt=" Wkiom1ie04xsqrb3aaakltbnbfi369.png "/>

Three. Edit the server configuration file from C vim/etc/my.cnf

[Mysqld_multi]

Mysqld=/usr/bin/mysqld_safe

Mysqladmin=/usr/bin/mysqladmin

User=root

password=123456

Log=/tmp/multi.log

[mysqld157]

port=3306

Datadir=/var/lib/mysqla

Pid-file=/var/lib/mysqla/mysqld.pid

Socket=/var/lib/mysqla/mysql.sock

User=mysql

server-id=87

[MYSQLD100]

port=3307

Datadir=/var/lib/mysqlb

Pid-file=/var/lib/mysqlb/mysqld.pid

Socket=/var/lib/mysqlb/mysql.sock

User=mysql

server-id=87

Description

• Add [Mysqld_multi] module, call or Mysqld_safe

• Mysqladmin where the tool for changing passwords is stored

• Log in as Root

• Define a log file

• Add and master one communication module [mysql157],157 Digital custom naming

• Specify a port of 3306

• Data storage Directory

• PID file directory

• Socket Directory

• Logged-on user

• Server-id

• Except for the same server-id (because the IDs do not conflict in different groups), the other and the primary two cannot be the same

Four. From the server, initialize the Mysqla and mysqlb, create the corresponding directory, and modify the owner to MySQL, use the/usr/bin/mysql_install_db

/usr/bin/mysql_install_db--datadir=/var/lib/mysqla

/usr/bin/mysql_install_db--datadir=/var/lib/mysqlb

Chown-r mysql/var/lib/mysqla/

Chown-r mysql/var/lib/mysqlb/

Five. Start MySQL, use Mysql_multi, specify configuration files and modules

Mysqld_multi--defaults-file=/etc/my.cnf Start 157

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/8D/82/wKiom1ie07KiVr6XAABHstbHGmw085.png "title=" 51. PNG "alt=" Wkiom1ie07kivr6xaabhstbhgmw085.png "/>

Mysqld_multi--defaults-file=/etc/my.cnf Start 100

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/8D/7F/wKioL1ie08HwPkPLAABApnXriWM113.png "title=" 52. PNG "alt=" Wkiol1ie08hwpkplaabapnxriwm113.png "/>

Six. To log in to MySQL, specify the port number and socket file

Log in to mysql157 and add the Master.info to the primary communication, turn on salve, and view the status:

mysql-p 3306-s/var/lib/mysqla/mysql.sock

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/8D/82/wKiom1ie09SxU7QeAAAXq8FNgNY763.png "style=" float : none; "title=" 61.png "alt=" Wkiom1ie09sxu7qeaaaxq8fngny763.png "/>

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/8D/7F/wKioL1ie09SgEEzXAABAWuJm_Bc862.png "style=" float : none; "title=" 611.png "alt=" Wkiol1ie09sgeezxaabawujm_bc862.png "/>

Log in to mysql100 and add the Master.info to the primary two communication, turn on salve, and view the status:

mysql-p 3307-s/var/lib/mysqlb/mysql.sock

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/8D/82/wKiom1ie0_bja6i7AAAXMui06dY512.png "style=" float : none; "title=" 62.png "alt=" Wkiom1ie0_bja6i7aaaxmui06dy512.png "/>

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/8D/7F/wKioL1ie0_eSpNtfAABAguGfFTY307.png "style=" float : none; "title=" 621.png "alt=" Wkiol1ie0_espntfaabagugffty307.png "/>

Seven. Primary one and primary two create databases DB1 and DB2, respectively, log on from the server 157, 100 can display synchronized created

Main one Create DB1:

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/8D/82/wKiom1ie1CjyMiPKAAAbhvJLddg463.png "title=" 71. PNG "alt=" Wkiom1ie1cjymipkaaabhvjlddg463.png "/>

Main two create DB2:

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/8D/7F/wKioL1ie1Daz_wDiAAAbhAao7bo777.png "title=" 72. PNG "alt=" Wkiol1ie1daz_wdiaaabhaao7bo777.png "/>

Login from 157 to view:

Mysql-p 3306-s/var/lib/mysqla/mysql.sock

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/8D/82/wKiom1ie1EiyXQOhAAAQUw9swJo586.png "title=" 73. PNG "alt=" Wkiom1ie1eiyxqohaaaquw9swjo586.png "/>

Login from 100 to view:

Mysql-p 3307-s/var/lib/mysqlb/mysql.sock

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/8D/82/wKiom1ie1FbygvynAAAP_K96dtQ553.png "title=" 74. PNG "alt=" Wkiom1ie1fbygvynaaap_k96dtq553.png "/>

This article is from "A horse on the Plains" blog, please be sure to keep this source http://huangzp.blog.51cto.com/12434999/1896920

mysql-database-Multi-machine configuration