Mysql master-slave and mysql master-master and semi-synchronous replication

I. Preparation (both master and slave need to be configured): yum-y install mysql mysql-server # install mysql yum-y install ntpdate # installation time synchronization echo'* / 1 * / usr/sbin/ntpdate ntp1.aliyun.com & > / dev/null' > > / var/spool/cron/root # configure network time synchronization service mysqld start # start the service chkconfig-- add mysqld # add the default run level for the system service chkconfig mysqld on # Master server configuration: the main configuration file defaults to: / etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 log-bin=master-bin # Open binary log file server-id=1 # set server id Guarantee globally unique sync-binlog=1 # globally unique server-id innodb-flush-logs-at-trx-commit=1 innodb-support-xa=1 service mysqld restart # restart service mysql > show global variables like'% server%' # Database id mysql > show master logs; # View database binary log mysql > show master status; # check the status of the master server and remember the binary log name. Configure the slave server using mysql > grant replication slave,replication client on *. * to 'copy'@'192.168.0.50' identified by' 123.com' # authorize the copy user on the 192.168.0.50 host to copy all libraries mysql > select Host,User,Password from mysql.user # View users 3. Slave server configuration: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 relay-log=slave-log # enable relay log server-id=10 # server id Globally unique read-only=1 # sets the server to read-only mode (not valid for users with root privileges) service mysqld restart # restart the service mysql > change master to master_host='192.168.0.10',master_user='copy', master_password='123.com',master_log_file='master-bin.000001' Master_log_pos=590 Master_host # Primary server address master_user # user with copy permission master_password # password master_log_file # from which binary log to copy master_log_pos # where does the binary log start Copy mysql > start slave # enable slave server mysql > show slave status\ G; # View slave server status 4. Verify master server: mysql > create database db_test; # create database slave server: mysql > show databases # check whether the server is synchronized. 5. Master master replication server A: [mysqld] # add content log-bin=master-bin # enable binary log relay-log=slave-log # enable relay log server-id=1 # server id Global unique auto-increment-offset=1 # self-increment starting auto-increment-increment=2 # self-increment one step (for example: 1, 3, 5, 7, 9...) Mysql > grant replication slave,replication client on *. * to 'copy'@'192.168.0.50' identified by' 123.compositions; mysql > change master to master_host='192.168.0.50',master_user='copy', master_password='123.com',master_log_file='master-bin.000001',master_log_pos=106; mysql > start slave; mysql > show slave status\ G Server B: [mysqld] # add content log-bin=master-bin relay-log=slave-log server-id=10 auto-increment-offset=2 # since Increment start auto-increment-increment=2 # step by step from increment (example: 2pr 4 6, 8, 10.) Mysql > grant replication slave,replication client on *. * to 'copy'@'192.168.0.10' identified by' 123.compositions; mysql > change master to master_host='192.168.0.10',master_user='copy', master_password='123.com',master_log_file='master-bin.000003',master_log_pos=106; mysql > start slave; mysql > show slave status\ G 6. Semi-synchronous replication if you want to use semi-synchronous replication, you must meet the following conditions: 1. MySQL 5.5 and above version 2. The variable have_dynamic_loading is YES 3. Asynchronous replication already exists: mysql > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; # install plug-in mysql > show plugins; # check whether the plug-in is installed successfully mysql > SET GLOBAL rpl_semi_sync_master_enabled = 1; # enable semi-synchronous mysql > show status like' Rpl_semi_sync_master_status' # check whether you are running to install the plug-in mysql > show plugins; mysql > SET GLOBAL rpl_semi_sync_slave_enabled = 1 from: mysql > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; # # enable semi-synchronous mysql > STOP SLAVE # turn off replication thread mysql > START SLAVE # start replication thread mysql > show status like 'Rpl_semi_sync_slave_status'; # check whether mysql > show variables like'% Rpl%' is running # check the server variable rpl_semi_sync_master_enabled # related to semi-synchronization # whether semi-synchronous rpl_semi_sync_master_timeout is enabled # wait for timeout rpl_semi_sync_master_wait_for_slave_count # how many slave responses do you need to wait To return to the client mysql > show status like'% Rpl_semi%' # semi-synchronous status Rpl_semi_sync_master_clients # current number of semi-synchronous replication slaves 7, replication-related files slave node: master.info: used to save relevant information (account number, password, server address) when slave connects to master Copied binaries, etc.) relay-log.info: saves the location of the binaries that have been copied to on the current slave node, and the location of the relay-log