MySQL Configuration master-Slave synchronization

MySQL Configuration master-slave synchronization MySQL master server configuration

Vim/etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socksymbolic-links=0lower_case_table_names=1sql_mode=No_engine_substitution,strict_trans_tables#设置数据库标识Server-id=2#任何一个事务提交之后就立即写入到磁盘中的二进制文件sync_binlog=1#保持数据一致性, it is recommended to configureinnodb_flush_log_at_trx_commit=1#开启服务器二进制日志Log-bin=mysql-bin# Memory allocated for each session, used to store the cache of binary logs during the transactionbinlog_cache_size=1M# master-slave copy format (mixed,statement,row, default format is statement)binlog_format=Mixed# Number of days the binary log is automatically deleted/expired. The default value is 0, which means that it is not automatically deleted. expire_logs_days=7# If only one database can be configured, otherwise do not recommend configuration, Bin-log position value does not change, affect synchronization#binlog-do-db=db1,db2binlog-ignore-db=mysql[Mysqld_safe]Log-error=/var/log/mysqld.logPid-file=/var/run/mysqld/mysqld.pid

MySQL from server configuration

vim /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socksymbolic-links=0lower_case_table_names=1sql_mode=#设置数据库标识server-id=1replicate-do-db=db1replicate-ignore-db=mysql[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

Configure Master-Slave

Master DB Server ip:192.168.1.216
Slave DB Server ip:192.168.1.217

# The master server creates an account to ensure that the slave server can access the master server databaseMysql >Grant Replication Slave on * * to [e-mail protected] identified by' [email protected] ';Mysql >Flush Privileges;Mysql >Show variables like' server_id ';Mysql >Show variables like'%bin ';# Primary Database lock table read-onlyMysql >Flush tables with Read lock;Mysql >Show Master Status;# Import a database from a libraryMysql >Change Master to master_host=' 192.168.1.216 ', master_port=3306,master_user=' backup ', master_password=' [email protected] ', master_log_file=' mysql-bin.00001 ', master_log_pos=1000,master_connect_retry=30;# View status, starting from library threadMysql >Show Slave Status\gMysql >Start slave;# View sync statusMysql >Show Slave Status\g# appearsslave_io_running: Yesslave_sql_running: Yes# that configuration was successful# Main Library unlock databaseMysql >Unlock tables;

MySQL Configuration master-Slave synchronization