MySQL both configuration experiment steps

Lab Environment:

Two MARIADB servers, IP: 172.16.2.16 and 172.16.2.17

The version of MARIADB is 5.5.36, which is installed using binary mode. The installation is complete.

/mydata/data Database File storage directory

/mydata/binlog/binary log file storage location

/mydata/relaylog/Relay Log storage location

The experimental project:

The two servers are built as two main models.

Experimental operation Process:

172.16.2.16 the configuration process

The/etc/my.cnf configuration file [mysqld] section is as follows

[mysqld]port = 3306socket = /tmp/mysql.sockskip-external-lockingkey_buffer_size =  256mmax_allowed_packet = 1mtable_open_cache = 256sort_buffer_size = 1mread _buffer_size = 1mread_rnd_buffer_size = 4mmyisam_sort_buffer_size = 64mthread_ Cache_size = 8query_cache_size= 16mthread_concurrency = 8auto-increment-increment  = 2                 Set the auto-grow value for each increment, this server has an odd growth value of auto-increment-offset = 1                set the initial value of the autogrow value log-bin=/mydata/binlog/mysql-bin         Modify binary log file storage location relay-log=/mydata/relaylog/relay-log     relay log to the location binlog_format= mixedserver-id= 1                        Use the default server-iddatadir=/mydata/data          here       set the directory where the database is stored sync_binlog = 1             set binary log working features to write logs to binary log files in real time

Authorizing the other node

MariaDB [(None)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT on * * to [e-mail protected] ' 172.16.2.17 ' identified by ' s Lavepass '; MariaDB [(None)]> FLUSH privileges;

172.16.2.17 Node configuration information

The/etc/my.cnf configuration file [mysqld] section is as follows

[Mysqld]port = 3306socket =/tmp/mysql.sockskip-external-lockingkey_buffer_size = 256mmax_allowed_packet = 1Mtable_ Open_cache = 256sort_buffer_size = 1mread_buffer_size = 1mread_rnd_buffer_size = 4mmyisam_sort_buffer_size = 64Mthread_ Cache_size = 8query_cache_size= 16mthread_concurrency = 8log-bin=/mydata/binlog/mysql-binrelay-log=/mydata/relaylog  /relay-logbinlog_format=mixedserver-id= 2 here Server-id for 2datadir=/mydata/dataauto-increment-increment = 2auto-increment-offset = 2

Authorizing the other node

MariaDB [(None)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT on * * to [e-mail protected] ' 172.16.2.16 ' identified by ' s Lavepass '; MariaDB [(None)]> FLUSH privileges;

Connect each node to each other, and before connecting to each other, you need to know the location of the binary log file and the transaction in the file that you are currently using.

In node one view current binary log information, get the log file used for mysql-bin.000012, the latest event ID is 676

MariaDB [(None)]> SHOW MASTER STATUS; [Object Object]

Connect the first node on the second node

MariaDB [(None)]> change MASTER to master_host= ' 172.16.2.16 ', master_user= ' slaveuser ', master_password= ' Slavepass ' , master_log_file= ' mysql-bin.000012 ', master_log_pos=676; MariaDB [(None)]> START SLAVE;

In node one view current binary log information, get the log file used for mysql-bin.000006, the latest event ID is 245

MariaDB [(None)]> SHOW MASTER STATUS;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s3.51cto.com/wyfs02/m00/49/cf/wkiol1qa_marwep9aacvhy_r-k0841.jpg "title=" Clipboard.png "alt=" WKioL1Qa_ Marwep9aacvhy_r-k0841.jpg "/>

MariaDB [(None)]> change MASTER to master_host= ' 172.16.2.17 ', master_user= ' slaveuser ', master_password= ' Slavepass ' , master_log_file= ' mysql-bin.000006 ', master_log_pos=245; MariaDB [(None)]> START SLAVE;

After the configuration is complete, test:

1, create the database on the first node, and then view the second node if there is a description of replication success.

2, create a table on the second node for the first node created, and on the first node, if there is a description of bidirectional replication, there is no problem.

This article is from the "beauty of the long-lost Pride" blog, please be sure to keep this source http://wukui.blog.51cto.com/1080241/1554971

MySQL both configuration experiment steps