MySQL Master-Slave synchronization

MY.CNF configuration file/etc/my.cnfmysql Database location DATADIR=/VAR/LIB/MYSQL1, modify the data file storage path of the MySQL database cp/usr/share/mysql/my-default.cnf/ ETC/MY.CNF (If/etc is not my.cnf) Deactivate mysqlservice mysql stop check whether it is all closed: Ps-ef |grep mysql Build data directory: Mkdir-p/data/db/mysql copy entire/var /lib/mysql directory: Cp-r/var/lib/mysql/*/data/db/mysql/Backup before the mysql:mv/var/lib/mysql/var/lib/mysql.back create MySQL directory: mkdir/ Var/lib/mysql Modify the configuration file: The VIM/ETC/MY.CNF configuration is as follows: [client] #socket =/data/db/mysql/mysql.sock[mysqld]datadir =/data/db/mysql # port = 3306#socket =/data/db/mysql/mysql.sock Create soft connection: Ln-s/var/lib/mysql/mysql.sock/data/db/mysql/ Mysql.sock Modify directory Permissions: chown-r mysql:mysql/data/db/mysqlchown-r mysql:mysql/var/lib/mysql restart MySQL service: service MySQL Start if you can start the success instructions are configured correctly, otherwise reconfigure and check for permissions issues (seemingly PHP also has an impact, not verified: Modify/etc/php.ini# to find Mysql.default_socket =# changed to Mysql.default_ Socket =/data/mysql/mysql.sock) 2, configure master-slave synchronization to establish a sync account on the primary server grant REPLICATION slave,file on * * to ' REPLICATION ' @ ' 10.1.1.% ' Identified by ' 123456 ' with GRANT option;//grant all privileges on * * to ' mvcuser ' @ "%" identified By ' 123456 ' with GRANT OPTION; FLUSH privileges; If there are more than one need to sync, you need to set up multiple accounts Grant REPLICATION SLAVE on * * to ' synchronous101 ' @ ' 10.169.111.66 ' identified by ' XXXXXX '; FLUSH privileges; Given slave permissions, there are multiple slave machines that perform multiple checks to create whether a successful select user,host from Mysql.user; Modify the configuration of the primary server Vim/etc/my.cnf[mysqld] server-id=1 # host flag, Integer # Log-bin=mysql-binlog-bin=/data/db/mysql/binlog/mysql-binlog # Turn on Bin-log (path to make sure this file is writable according to your installation settings) Binlog-do-db=vragon _DEBUG # To synchronize database names, multiple write multiple lines binlog-ignore-db=mysql #不需要备份的数据库, multiple write multiple lines binlog-ignore-db=performance_schemabinlog-ignore-db =information_schema#read-only=0 # Host, read and write can be mkdir-p/data/db/mysql/binlogchown-r mysql:mysql/data/db/mysql/ Binlog Restart Mysql:service mysql Restart login master database, lock table, stop update mysql-uroot-pmysql> flush tables with read Lock;show master Statu S\g; or show Master status;*************************** 1. Row *************************** file:mysql-binlog.000001 position:120 binlog_do_db:vragon_debug Binlog_Ignore_DB: Mysql,performance_schema,information_schemaexecuted_gtid_set:1 row in Set (0.00 sEC) Note the file and position, set from the server when the # description, if the execution of this step is always empty set (0.00 sec), that the previous MY.CNF is not configured to. Open a new terminal, export data directly: mysqldump--add-drop-table-h 127.0.0.1-u root-p vragon_debug >/data/db/back/vragon_debug_ 2015-02-04.sql (#仅仅备份数据库结构: #mysqldump-uroot-p--no-data--databases databasename1 databasename2 databasename3 >/ data/db/back/structurebackupfile.sql# back up all databases on the server: #mysqldump-uroot-p--all-databases >/data/db/back/ ALLBACKUP.SQL) Primary database backup complete, resume write operation mysql> unlock tables;mysql> quit; Under the face of provisioning from the library to transfer packets from the database and data recovery from the library SCP-RVP Vragon_ Debug_2015-02-04.sql [email protected]:/data/db/back login from database mysql-uroot-p CREATE DATABASE:mysql> Vragon_debug ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_unicode_ci; restore Database Data mysql-hlocalhost-uroot-p Vragon_debug </dat A/db/back/vragon_debug_2015-02-04.sql (using database: Use ' mysql '; restore compressed MySQL database gunzip < backupfile.sql.gz | mysql- Uusername-ppassword DatabaseName Transferring a database to a new server Mysqldump-uusername-ppassword DatabaseName | Mysql–host=*.*.*.*-c databasename) modified fromLibrary configuration: vim/etc/my.cnf[mysqld] server-id=2 log-bin=/data/db/mysql/binlog/slave_mysql_binlog#binlog-do-db = dbname # Database name to synchronize Replicate-do-db=vragon_debug # Copy only a library Binlog-ignore-db=mysql # do not copy a library Binlog-ignore-db=information_schema # or Replicate-ignore-db=mysqlbinlog-ignore-db=performance_schema#slave-skip-errors=all # Better not skip the wrong #master-host= 10.169.123.186 # IP, username, password can not be set here #master-user=synchronous101#master-password=123456#log-slave-updates # Do not understand slave-net-timeout=60 # if the primary server is found to be disconnected from the server, the time difference (seconds) of reconnection #master-connect-retry=60 # reconnection maximum mkdir-p/data/db/mysql/binlog/ Chown-r mysql:mysql/data/db/mysql/binlog/Restart from library: service MySQL Restart verify that you can link on the main library: Mysql-h 10.169.123.186-u Synchronous101-p log on from the database:mysql-uroot-pmysql> stop slave;mysql> change master to master_host= ' 10.169.123.186 ' , master_user= ' synchronous101 ', master_password= ' XXXXXX ', master_log_file= ' mysql-binlog.000001 ', master_log_pos= 120;mysql> start slave;mysql> unlock tables;mysql> show slave status\g; Slave_io_running:yesslavE_sql_running:yes if the above two actions yes indicates that the configuration is successful. At this point, the master-slave database synchronization configuration is complete. You can add (INSERT) to delete (UPDATE) to the primary database and verify that the synchronization was successful!

MySQL master-slave synchronization