In Linux, Mysql is mutually active/standby.
System Environment: CentOS 6.5
Master1: 192.168.100.204
Master2: 192.168.100.205
Mysql version: mysql-5.6.19
Note: The configuration methods for versions earlier than mysql 5.5 and later are different.
Install the following two nodes:
1: Firewall opens the corresponding port
#/Sbin/iptables-I INPUT-p tcp -- dport 3306-j ACCEPT
#/Etc/rc. d/init. d/iptables save
2: Install related dependency packages
# Yum-y install gcc-c ++ ncurses-devel openssl-devel cmake perl lsof bison
3: delete the mysql
# Rpm-qa | grep mysql
# Rpm-e -- allmatches -- nodeps mysql-libs-5.1.71-1.el6.x86_64
4. Create related directories
# Mkdir/doiido/soft
# Mkdir-p/doiido/mysql/data/
5. Create a mysql user
#/Usr/sbin/groupadd mysql
#/Usr/sbin/useradd-s/sbin/nologin-M-g mysql
# Chown-R mysql: mysql/doiido/mysql/
6. Install mysql
# Cd/doiido/soft
# Tar-zxvf mysql-5.6.19.tar.gz
# Cd mysql-5.6.19
# Cmake-DCMAKE_INSTALL_PREFIX =/doiido/server/mysql-DMYSQL_DATADIR =/doiido/mysql/data-encoding = 1-runtime = 1-runtime = 1-DSYSCONFDIR =/etc/-DWITH_SSL = yes-DDEFAULT_CHARSET = utf8-DDEFAULT_COLLATION = utf8_general_ci-DWITH_READLINE = on
# Make & make install
7. Modify related directory permissions and create a soft connection
# Chmod + w/doiido/server/mysql
# Chown-R mysql: mysql/doiido/server/mysql/
# Ln-s/doiido/server/mysql/lib */usr/lib/
# Ln-s/doiido/server/mysql/bin/mysql/usr/bin
8. modify the configuration file.
# Cp/doiido/server/mysql/support-files/my-default.cnf/etc/my. cnf
# Vi/etc/my. cnf
[mysqld]character-set-server = utf8default-storage-engine = MyISAMbasedir = /doiido/server/mysqldatadir = /doiido/mysql/datalog-error = /doiido/mysql/mysql_error.logpid-file = /doiido/mysql/mysql.pidmax_allowed_packet = 32Mexplicit_defaults_for_timestamp = true
9. Install the mysql database.
#/Doiido/server/mysql/scripts/mysql_install_db -- basedir =/doiido/server/mysql -- datadir =/doiido/mysql/data -- user = mysql
10: Set mysql to automatically start the service
# Cp/doiido/server/mysql/support-files/mysql. server/etc/init. d/mysqld
# Chkconfig -- add mysqld
# Chkconfig -- level 345 mysqld on
11. Modify the mysqld file and start mysql
# Vi/etc/init. d/mysqld
# Modify the following two items in the mysqld file: basedir =/doiido/server/mysqldatadir =/doiido/mysql/data
# Service mysqld start
12: Create the database to be synchronized
# Mysql-uroot-p
Mysql> create database doiido;
Mysql> grant all privileges on doiido. * TO doiido @ "%" identified by 'doiido123 ';
Mysql> exit
-------------------- The following operations are performed on server master1
Modify configuration file
# Vi/etc/my. cnf
[Mysqld] # The default value is 1. If the master and slave cannot be the same, server-id = 1log-bin = bin. logport = 3306binlog-do-db = doiido # replicate-do-db = doiido
Restart mysql
# Service mysqld restart
Create an account for synchronization
# Mysql-uroot-p
Mysql> grant replication slave on *. * to 'dodo '@ '192. 168.100.205' identified by 'jjjjjjj ';
View the File value and record it
Mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| bin.000001 | 120 | doiido | | |+------------------+----------+--------------+------------------+-------------------+
Mysql> exit
-------------------- The following operations are performed on server master2
2. modify the configuration file my. cnf on the slave.
# Vi/etc/my. cnf
[mysqld]server-id=2port=3306log-bin=bin.logbinlog-do-db =doiidoreplicate-do-db =doiido
Restart mysql
# Service mysqld restart
Create a database on the slave server
# Mysql-uroot-p
Set 192.168.100.204 as your master server
Mysql> CHANGE MASTER
MASTER_HOST = '1970. 168.100.204 ', MASTER_PORT = 192,
MASTER_USER = 'dodo ',
MASTER_PASSWORD = 'jjjjjjj ',
MASTER_LOG_FILE = 'bin. 000001 ',
MASTER_LOG_POS = 120;
Enable slave Server replication
Mysql> start slave;
Mysql> show slave status \ G;
...................Slave_IO_Running: YesSlave_SQL_Running: Yes...................
Note:
# Slave_IO_Running: connects to the master database and reads logs from the master database to the local computer to generate local log files.
# Slave_ SQL _Running: Read the local log file and execute the SQL command in the log.
Both of the above must be yes, and one of them is an error.
Mysql> exit
At this point, the one-way Master/Slave configuration is complete. If you need to be mutually Master/Slave, the configuration is as follows:
-------------------- The following operations are performed on server master2
Create an account for synchronization
# Mysql-uroot-p
Mysql> grant replication slave on *. * to 'dodo '@ '192. 168.100.204' identified by 'jjjjjjj ';
View the File value and record it
Mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| bin.000001 | 649 | doiido | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
Mysql> exit
-------------------- The following operations are performed on server master1
Set 192.168.100.205 as your master server
Mysql> CHANGE MASTER
MASTER_HOST = '1970. 168.100.205 ',
MASTER_PORT = 3306,
MASTER_USER = 'dodo ',
MASTER_PASSWORD = 'jjjjjjj ',
MASTER_LOG_FILE = 'bin. 000001 ',
MASTER_LOG_POS = 649;
Enable slave Server replication
Mysql> start slave;
Mysql> show slave status \ G;
...................Slave_IO_Running: YesSlave_SQL_Running: Yes...................
Both of the above must be yes, and one of them is an error.
Mysql> exit
The master-slave configuration is complete.
