Planning hosts:IP node type192.168.1.100 Master/slave192.168.1.101 Master/slave192.168.1.102 Slave192.168.1.103 SlaveThe operating system is centos6.5
schematic diagram:
1. Install MySQL (192.168.1 ) separately. 100~103)
1) Download Repo,wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm or from the MySQL Yum repository (http://dev.mysql.com/ downloads/repo/yum/) manual download 2) install Repo:yum Localinstall mysql-community-release-el6-5.noarch.rpm
The following command can be used to confirm that the repository was successfully added: Yum repolist enabled | grep community3) Yum install Mysql-community-server
2. Configuration file (192.168.1. 100~103) Note: This configuration is a common configuration, after configuration, each node can be the primary node, or from the node, the key is whether to enablevim/etc/my.cnf[Client]socket=/root/data/mysql/mysql.sock[mysqld]datadir=/root/data/mysql #修改默认datadirsocket =/root/data/mysql/ Mysql.sockserver_id=100#每台主机不一样, according to the actual situation fill in unique Idlog_bin=mysql_bin_logbinlog_format=rowrelay_log = Mysql_relay_bin_loglog_slave_updates = 1read_only = 1slave-skip-errors = 1062,1032,1060#skip some errors such as keys Conflict,data ' s not exists,table already exists modify default DataDir:Vim/etc/init.d/mysqldModify this behavior: get_mysql_option mysqld datadir "/root/data/mysql" Set up data save directory and give read and Write permission chmod 755/rootmkdir-p/root/data/mysqlchown -R mysql:mysql/root/data/mysqlchgrp-r Mysql/root/data/mysqlchmod-r 755/root/data/
3. Initialize the table and start MySQLmysql_install_db--user=mysql Service mysqld start
4. Account ConfigurationOn the master host (
192.168.1.100/101) Configure replication dedicated account REPL and authorize create user ' repl ' @ ' 10.% ' identified by ' repl '; GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 10.% ' identified by ' repl '; note: It is recommended to also configure SLAVE users on REPL, which facilitates master-slave switching of the main node (
192.168.100/101) Read and write passwords, do not set the write password and remote login permissions from the node, prevent misuse of the slave query process to write statements that cause the data to be out of sync with the Create user ' write ' @ '% ' identified by ' 123456 ‘; Grant all privileges on * * to ' write ' @ '% ' identified by ' 123456 '; flush privileges; In the slave node (
192.168.1.102/103) Configure replication read-only dedicated account readcreate user ' read ' @ '% ' identified by ' 123456 '; GRANT Select On * * to ' read ' @ '% ' identified by ' 123456 ';
5. Boot from server replicateIn addition to the 192.168.1.101 host to synchronize from the 192.168.1.100, all others are synchronized from the 192.168.1.101 (see schematic)
Stop slave;
Change MASTER to master_host= '192.168.1.101',
master_user= ' Repl ',
master_password= ' Repl ',
master_log_file= ' mysql-bin.000001 ',master_log_pos=0;start slave;View slave node Status: Show slave status \g The first configuration generally occurs with error 1:error 1:got fatal error 1236 from master If reading data from binary log: ' Could not ' find first log file name in B
On the master side, execute:flush logs; Show master status;
+----------------------+------------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+----------------------+------------+--------------+------------------+-------------------+
| mysql_bin_log.000002 | 120| | | |
+----------------------+------------+--------------+------------------+-------------------+
Write down file, Position. On the slave side, perform:
Stop slave;
Change MASTER to master_log_file= ' mysql_bin_log.000002 ', master_log_pos=120;
Start slave;
View status:show slave status \g
Success:*************************** 1. Row ***************************slave_io_state:waiting for Master to send eventmaster_host:192.168.1.101Master_user:replmaster_port:3306connect_retry:60master_log_file:mysql_bin_log.000006read_master_log_pos:120relay_log_file:mysql_relay_bin_log.000002relay_log_pos:287relay_master_log_file:mysql_bin_log.000006Slave_io_running:yesSlave_sql_running:yes.....
6. How do I skip an error? Fatal error 2:last_sql_errno:1418Last_sql_error:error ' This function has none of the deterministic, NO SQL, or READS SQL DATA in its Declarati On and binary logging are enabled (*might* want to use the less safe
Log_bin_trust_fu nction_creators
variable) ' on query.Workaround one: Handle the temporary error, and proactively skip an erroneous transaction (this is what happens when I create a new function, other things can be handled similarly)Stop slave;
Set GLOBAL sql_slave_skip_counter=1;
Start slave;If this type of error is large, then ignore the errors in the configuration, and Vim etc/my.cnf add 1418 to the slave-skip-errors:slave-skip-errors =.., 1418 #跳过1418错误Restart Mysql:serveice mysqld restart; Solution Two: Show variables like 'log_bin_trust_function_creators'; Discovery is off state, add a line to the config file Log_bin_trust_function_creators=1
7. PrecautionsDo not use the self-increment field as the primary key when the table is built, otherwise the primary key conflict will inevitably occur when the two machines synchronize with each other, resulting in synchronization failure or data loss, we recommend using the GUID (UUID) as the primary key
Quickly install and configure MySQL replication dual master cluster--and change data save directory
