Linux Mysql Database one-way synchronization configuration method sharing _mysql

Also called the Master from replication, is done through binary log files, note: MySQL database version, two database versions to the same

System environment:
Master server Master ip:192.168.0.88
Slave ip:192.168.0.99 from server

One-way sync
Note the main points
1, only need to sync the database backup to upload from the database, no other things need
2. When you restore the database from the library, you must first build a database with the same name, or restore will prompt errors
3, in the development of the update point, must be in the synchronization process has not started before the operation

Configure on the primary server

Modifying the MY.CNF requires that the database settings be restarted before it takes effect, skipping this step if you do not want to limit the synchronization of the specified database

[root@kt /]# service mysqld stop

Stop Mysql service

[root@kt /]# vim/etc/my.cnf

Edit Mysql profile Add the following field, note: These two fields do not have their own by default in the Server-id field to add it

Binlog-do-db = kangte	Binary kangte databases that need to be synchronized
binlog-ignore-db = MySQL	Binary MySQL database not synchronized

[Root@kt/]# Service mysqld start

Start the primary server

To establish a remote synchronization user

mysql> Grant replication slave,replication client,reload,super on *. * to 'kt' @ '192.168.0.99' identified by '123456 ' withgrant option;

mysql> flush Privileges;	Update database for user to take effect
[Root@zzh/]# mysql-h 192.168. 0- u kt-p	Test the build users on the backup server to see if they can log in

Mysql> flush tables with read lock;	Set Read lock
Mysql> Show master status;	View the current binary log name and offset values, starting with this point in the library for data recovery

See the results as follows:

+------------------+----------+--------------+------------------+

| File Position binlog_do_db binlog_ignore_db |

+------------------+----------+--------------+------------------+

| mysql-bin.000006 6 c22>|

+------------------+----------+--------------+------------------+

[root@zzh /]# mysqldump-u root -P kangte > /kangte. SQL	Back up the specified database, or you can use the physical backup directly
mysql> unlock tables;	Unlock read lock
[Root@zzh/]# SCP /kangte. sql root@192.168. 0.:/	To upload a backup from a database server

Backup Server Configuration

Note: If the database has the same database to delete the previous library

[Root@zzh/]# service mysqld stop

Stop Mysql service

[root@zzh /]# vim/etc/my.cnf

Edit Mysql profile Add the following fields, Note: These fields do not have their own by default in the Server-id field to add it

Server-id = 2	Note: Remove the annotation and shield the server-id=1 above
Master-host =192.168.0. the	Specify the primary server IP address
Master-user = kt	Develop user names that can be synchronized on the primary server
Master-password = 123456	Password
Master-port = 3306	Ports Used for synchronization
Master-connect-retry =	Breakpoint Reconnect Time
replicate-do-db = Kangte	Binary kangte databases that need to be synchronized
replicate-ignore-db = MySQL	Binary MySQL database not synchronized

Start from server

[Root@zzh/]#/usr/local/mysql/bin/mysqld_safe --skip-slave-start &

--skip-slave-start

Start from the database, do not immediately start the replication process from the database service

Mysql> CREATE Database kangte;	Note: Be sure to create the kangte Library before it can be restored
[Root@zzh/]# mysql-u root-p kangte < /kangte. sql	Restore Database

Specify an update point (note: When performing the specified update point below, be sure to operate when the synchronization process is not started)

Mysql> Change MASTER to

-> master_log_file= 'mysql-bin.000006', # Check the binary log name on the primary server

-> master_log_pos=656; # The offset value checked on the primary server

mysql> start slave;

Start the synchronization process

Check for sync connectivity

Mysql> show slave status\ G;

Slave_io_running : Yes	Network connection is normal
Slave_sql_running: Yes	Database structure is normal

MySQL one-way synchronous implementation command line operation

Instance Host:
dbasky=192.168.1.120
dbaskyback=192.168.1.121

Objective: To dbaskyback the host to synchronize the data on the Dbasky host test library

installing 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.
Create a user
Mysql>create database test;
Mysql>grant all on *.* to xu@192.168.1.121 identified by 123456;

[Root@dbasky] #vi/etc/my.cnf
Server-id=1 #为master
Log-bin=/var/log/mysql/mysql.log
Add to
Binlog-do-db=test #要同步的数据库名字

Restart MySQL
View with Mysql>show Master status

On the Dbaskyback machine.
Dbaskyback#vi/etc/my.cnf
server-id=2 #为slave
master-host=192.168.1.120
Master-user=xu
master-password=123456
master-port=3306
Master-connect-retry=60 #间隔60秒重新设置
Replicate-do-db=test

Use mysql>show slave status to see the synchronization situation, if there are errors can also be seen.