linux mysql database one way synchronization configuration method sharing
- 2020-05-13 03:33:40
- OfStack
Also known as master-slave replication, this is done using binary log files. Note: the version of mysql database is the same as the two database versions
System environment:
Master server master IP: 192.168.0.88
Slave server slave IP: 192.168.0.99
One-way synchronization
Pay attention to the point
1. You only need to transfer the database backup to the slave database that needs to be synchronized, and nothing else is needed
2. When restoring the database from the library, 1 must first build the database with the same name, otherwise the restore will prompt an error
3. When setting the update point, 1 must operate before the synchronization process is started
Configuration on the primary server
Changes to my.cnf require a restart of the database Settings to take effect, and if you don't want to restrict the specified database synchronization, skip this step [root@kt /]# service mysqld stop Discontinue Mysql service [root@kt /]# vim /etc/my.cnf Edit the Mysql configuration file to add the following fields. Note: these two fields are not added by default under the server-id field themselves binlog-do-db = kangte Base 2 requires a synchronized kangte database binlog-ignore-db = mysql Base 2 does not want to synchronize the MySQL database [root@kt /]# service mysqld start Start the primary server Establish remote sync user mysql > grant replication slave,replication client,reload,super on *.* to 'kt'@'192.168.0.99' identified by '123456'with grant option; mysql > flush privileges; Update the database to allow users to take effect [root@zzh /]# mysql -h 192.168.0.88 -u kt -p On the backup server, test whether the user under build 1 can log in mysql > flush tables with read lock; Set the read lock mysql > show master status; View the current 2-base log name and offset value and start data recovery from the library at this point See the results below: +------------------+----------+--------------+------------------+ | File Position Binlog_Do_DB Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000006 656 | +------------------+----------+--------------+------------------+ [root@zzh /]# mysqldump -u root -p kangte > /kangte.sql Backup specified database, you can also use physical backup directly mysql > unlock tables; Remove read lock [root@zzh /]# scp /kangte.sql root@192.168.0.99:/ Transfer the backup to the server from the database
Backup server configuration
Note: delete the previous library if the database has the same database [root@zzh /]# service mysqld stop Discontinue Mysql service [root@zzh /]# vim /etc/my.cnf Edit the Mysql configuration file to add the following fields. Note: these fields are not added by default under the server-id field themselves server-id = 2 Note: uncomment and mask server-id =1 above master-host =192.168.0.88 Specify the primary server IP address master-user = kt Specify a user name that can be synchronized on the primary server master-password = 123456 password master-port = 3306 The port used for synchronization master-connect-retry = 60 Breakpoint reconnection time replicate-do-db = kangte Base 2 requires a synchronized kangte database replicate-ignore-db = mysql Base 2 does not want to synchronize the MySQL database Start the slave server [root@zzh /]# /usr/local/mysql/bin/mysqld_safe --skip-slave-start & --skip-slave-start Start the slave database, do not immediately start the replication process from the database service mysql > create database kangte; Note: 1 you must create the kangte library first or it will not be restored [root@zzh /]# mysql -u root -p kangte < /kangte.sql Recovery database Specify the update point (note: when executing the update point specified below, 1 must be done when the synchronization process is not started) mysql > CHANGE MASTER TO - > MASTER_LOG_FILE=' mysql-bin.000006 ', # the binary log name to look up on the primary server - > MASTER_LOG_POS = 656; Look up the offset value on the primary server mysql > start slave; Start the synchronization process Check for synchronization connectivity mysql > show slave status\G; Slave_IO_Running : Yes Network connection is normal Slave_SQL_Running: Yes Database structure is normal
MySQL unidirectional synchronization implements command line operations
Instance host:
dbasky=192.168.1.120
dbaskyback=192.168.1.121
Purpose: the dbaskyback host synchronizes data on the dbasky host test library
Install mysql
[root@dbasky]#wget ftp://ftp.cronyx.ru/pub/FreeBSD/ports/distfiles/mysql-5.0.45.tar.gz
[root@dbasky]#cd /usr/local/mysql-5.0.45
[root@dbasky]#groupadd mysql
[root@dbasky]#useradd -g mysql mysql
[root@dbasky]#mkdir /opt/mysql-data
[root@dbasky]#CFLAGS="-O3 " CXX=gcc CXXFLAGS="-O3 -felide-constructors
-fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --enable-assembler --with-charset=utf8 --with-extra-charsets=gbk,gb2312,latin1 --localstatedir=/opt/mysql-data --with-mysqld-user=mysql --enable-large-files --with-big-tables --without-debug --enable-thread-safe-client --with-fast-mutexes --with-innodb
[root@dbasky]#make
[root@dbasky]#make install
[root@dbasky]#cd /etc
[root@dbasky]#rz my.cnf
[root@dbasky]#chown -R mysql .
[root@dbasky]#chgrp -R mysql .
[root@dbasky]#chown -R mysql /opt/mysql-data
[root@dbasky]#chgrp -R mysql /opt/mysql-data
[root@dbasky]#bin/mysql_install_db --user=mysql
[root@dbasky]#chown -R root .
[root@dbasky]#bin/mysqld_safe --user=mysql &
[root@dbasky]#cd /usr/local/mysql
[root@dbasky]#echo "PATH=/usr/local/mysql/bin:$PATH" > > /etc/profile
[root@dbasky]#echo "export PATH" > > /etc/profile
[root@dbasky]#echo "alias vi="vim"" > > /etc/profile
[root@dbasky]#echo "/usr/local/mysql/lib/mysql" > /etc/ld.so.conf.d/mysql.conf
On the dbasky machine
Establish user
mysql > create database test;
mysql > grant all on *.* to xu@192.168.1.121 identified by 123456;
[root@dbasky]#vi /etc/my.cnf
# for master server - id = 1
log-bin=/var/log/mysql/mysql.log
add
binlog-do-db =test # the name of the database to synchronize
Restart mysql
Use mysql > show master status
On the dbaskyback machine
dbaskyback#vi /etc/my.cnf
# for slave server - id = 2
master - host = 192.168.1.120
master - user = xu
master - password = 123456
master - port = 3306
master-connect-retry =60 # reset interval 60 seconds
replicate do - db = test
Use mysql > show slave status look at the synchronization and see if there are any errors.
System environment:
Master server master IP: 192.168.0.88
Slave server slave IP: 192.168.0.99
One-way synchronization
Pay attention to the point
1. You only need to transfer the database backup to the slave database that needs to be synchronized, and nothing else is needed
2. When restoring the database from the library, 1 must first build the database with the same name, otherwise the restore will prompt an error
3. When setting the update point, 1 must operate before the synchronization process is started
Configuration on the primary server
Changes to my.cnf require a restart of the database Settings to take effect, and if you don't want to restrict the specified database synchronization, skip this step [root@kt /]# service mysqld stop Discontinue Mysql service [root@kt /]# vim /etc/my.cnf Edit the Mysql configuration file to add the following fields. Note: these two fields are not added by default under the server-id field themselves binlog-do-db = kangte Base 2 requires a synchronized kangte database binlog-ignore-db = mysql Base 2 does not want to synchronize the MySQL database [root@kt /]# service mysqld start Start the primary server Establish remote sync user mysql > grant replication slave,replication client,reload,super on *.* to 'kt'@'192.168.0.99' identified by '123456'with grant option; mysql > flush privileges; Update the database to allow users to take effect [root@zzh /]# mysql -h 192.168.0.88 -u kt -p On the backup server, test whether the user under build 1 can log in mysql > flush tables with read lock; Set the read lock mysql > show master status; View the current 2-base log name and offset value and start data recovery from the library at this point See the results below: +------------------+----------+--------------+------------------+ | File Position Binlog_Do_DB Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000006 656 | +------------------+----------+--------------+------------------+ [root@zzh /]# mysqldump -u root -p kangte > /kangte.sql Backup specified database, you can also use physical backup directly mysql > unlock tables; Remove read lock [root@zzh /]# scp /kangte.sql root@192.168.0.99:/ Transfer the backup to the server from the database
Backup server configuration
Note: delete the previous library if the database has the same database [root@zzh /]# service mysqld stop Discontinue Mysql service [root@zzh /]# vim /etc/my.cnf Edit the Mysql configuration file to add the following fields. Note: these fields are not added by default under the server-id field themselves server-id = 2 Note: uncomment and mask server-id =1 above master-host =192.168.0.88 Specify the primary server IP address master-user = kt Specify a user name that can be synchronized on the primary server master-password = 123456 password master-port = 3306 The port used for synchronization master-connect-retry = 60 Breakpoint reconnection time replicate-do-db = kangte Base 2 requires a synchronized kangte database replicate-ignore-db = mysql Base 2 does not want to synchronize the MySQL database Start the slave server [root@zzh /]# /usr/local/mysql/bin/mysqld_safe --skip-slave-start & --skip-slave-start Start the slave database, do not immediately start the replication process from the database service mysql > create database kangte; Note: 1 you must create the kangte library first or it will not be restored [root@zzh /]# mysql -u root -p kangte < /kangte.sql Recovery database Specify the update point (note: when executing the update point specified below, 1 must be done when the synchronization process is not started) mysql > CHANGE MASTER TO - > MASTER_LOG_FILE=' mysql-bin.000006 ', # the binary log name to look up on the primary server - > MASTER_LOG_POS = 656; Look up the offset value on the primary server mysql > start slave; Start the synchronization process Check for synchronization connectivity mysql > show slave status\G; Slave_IO_Running : Yes Network connection is normal Slave_SQL_Running: Yes Database structure is normal
MySQL unidirectional synchronization implements command line operations
Instance host:
dbasky=192.168.1.120
dbaskyback=192.168.1.121
Purpose: the dbaskyback host synchronizes data on the dbasky host test library
Install mysql
[root@dbasky]#wget ftp://ftp.cronyx.ru/pub/FreeBSD/ports/distfiles/mysql-5.0.45.tar.gz
[root@dbasky]#cd /usr/local/mysql-5.0.45
[root@dbasky]#groupadd mysql
[root@dbasky]#useradd -g mysql mysql
[root@dbasky]#mkdir /opt/mysql-data
[root@dbasky]#CFLAGS="-O3 " CXX=gcc CXXFLAGS="-O3 -felide-constructors
-fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --enable-assembler --with-charset=utf8 --with-extra-charsets=gbk,gb2312,latin1 --localstatedir=/opt/mysql-data --with-mysqld-user=mysql --enable-large-files --with-big-tables --without-debug --enable-thread-safe-client --with-fast-mutexes --with-innodb
[root@dbasky]#make
[root@dbasky]#make install
[root@dbasky]#cd /etc
[root@dbasky]#rz my.cnf
[root@dbasky]#chown -R mysql .
[root@dbasky]#chgrp -R mysql .
[root@dbasky]#chown -R mysql /opt/mysql-data
[root@dbasky]#chgrp -R mysql /opt/mysql-data
[root@dbasky]#bin/mysql_install_db --user=mysql
[root@dbasky]#chown -R root .
[root@dbasky]#bin/mysqld_safe --user=mysql &
[root@dbasky]#cd /usr/local/mysql
[root@dbasky]#echo "PATH=/usr/local/mysql/bin:$PATH" > > /etc/profile
[root@dbasky]#echo "export PATH" > > /etc/profile
[root@dbasky]#echo "alias vi="vim"" > > /etc/profile
[root@dbasky]#echo "/usr/local/mysql/lib/mysql" > /etc/ld.so.conf.d/mysql.conf
On the dbasky machine
Establish user
mysql > create database test;
mysql > grant all on *.* to xu@192.168.1.121 identified by 123456;
[root@dbasky]#vi /etc/my.cnf
# for master server - id = 1
log-bin=/var/log/mysql/mysql.log
add
binlog-do-db =test # the name of the database to synchronize
Restart mysql
Use mysql > show master status
On the dbaskyback machine
dbaskyback#vi /etc/my.cnf
# for slave server - id = 2
master - host = 192.168.1.120
master - user = xu
master - password = 123456
master - port = 3306
master-connect-retry =60 # reset interval 60 seconds
replicate do - db = test
Use mysql > show slave status look at the synchronization and see if there are any errors.