设:主库为master,从库为slave
1.备份主库数据(全库)，且记录下 master_log_file,master_log_pos，用于后面设置slave时指定
备份脚本: mysqldump --socket=/home/data/mysql/mysql.sock --single-transaction --master-data=2 -uroot -proot --all-databases > dbdump.sql
2.将主库备份的数据在从库中恢复
登录slave mysql 然后source /home/bak/dbdump.sql
3.主库中创建用于主从复制的用户
执行脚本：grant replication slave,replication client on *.* to rep_user@'slave' identified by 'user_rep';
3.配置slave，登录 slave mysql 执行
change master to master_host='master',master_user='rep_user',master_password='user_rep',master_port=3306,master_log_file='mysql-bin.000749',master_log_pos=18616572,master_connect_retry=30;
附配置文件：
master
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysql] port = 3306 socket = /home/data/mysql/mysql.sock default-character-set = utf8 [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. log_bin = /home/data/biglog/mysql-bin binlog_format = 'MIXED' binlog-ignore-db=information_schema expire_logs_days = 14 relay_log = /home/data/relaylog/mysql-relay-bin server-id = 15 #GTID gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates = 1 #slow log slow-query-log = on # 开启慢查询功能 slow_query_log_file = /home/data/slowlog/slow-query.log # 慢查询日志存放路径与名称 long_query_time = 10 # 查询时间超过1s的查询语句 # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... socket = /home/data/mysql/mysql.sock datadir = /home/data/mysql log_error = /var/log/mysql/error.log character_set_server = utf8 character_set_client = utf8 collation_server = utf8_general_ci max_connections = 1000 lower_case_table_names=1 # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESslave:
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [client] port = 3306 #default-character-set = utf8 [mysqld] #innodb_force_recovery = 3 # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin #innodb_force_recovery=1 log_bin = /home/data/binlog/mysql-bin binlog_format = 'MIXED' expire_logs_days = 14 relay_log = /home/data/relaylog/mysql-relay-bin slave_skip_errors = 1062 read_only = 1 #GTID gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates = 1 #slow log slow-query-log = on # 开启慢查询功能 slow_query_log_file = /home/data/slowlog/slow-query.log # 慢查询日志存放路径与名称 long_query_time = 8 # 查询时间超过1s的查询语句 # These are commonly set, remove the # and set as required. #basedir = datadir = /home/data/mysql/ port = 3306 server_id = 4 # server_id = ..... socket = /home/data/mysql/mysql.sock log_error = /var/log/mysql/error.log character_set_server = utf8 character_set_client = utf8 collation_server = utf8_general_ci max_connections = 1000 lower_case_table_names=1 # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysql] default-character-set = utf8 socket = /home/data/mysql/mysql.sock
转载于:https://www.cnblogs.com/yako/p/9293457.html
