- 主从服务器之前分别作以下操作：
- 确定主从关系（此处以192.168.1.140作主，192.168.1.50作从为例）
- 版本一致（mysql-5.6.23）
- 初始化系统表,停止mysql服务
- 修改主服务器master:（以下配置以双核CPU，4G RAM为例）
# vi /etc/my.cnf
# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
report-port = 3306
port = 3306
report-host = 192.168.1.140 //此参数可选，在统计主从数量时非常方便
pid-file = /tmp/mysql.pid
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/var
skip-external-locking
max_connections = 1000 //它指的是客户端连接的最大并发数
key_buffer_size = 16M //它指的是用于缓存MyISAM表索引块的缓冲区大小
max_allowed_packet = 64M //它指的是服务器与客户端之间通信时使用的缓冲区的最大大小
table_open_cache = 2048 //它指的是服务器能够同时打开的表的最大个数
join_buffer_size = 1M //根据join语句。1M足矣
sort_buffer_size = 2M //系统中对数据进行排序的时候用到的Buffer。2M-4M足够
net_buffer_length = 16K //它指的是服务器与客户端程序进行通信时使用的连接和结果缓冲区的初始大小。
read_buffer_size = 8M //指的是对表进行顺序扫描的那个线程所使用的缓存区的大小。缓冲区会根据每个客户端的需要进行分配
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 8M //在执行类似ALTER TABLE、CREATE INDEX和REPAIR TABLE这样的操作期间，需要分配缓冲区，以便对MyISAM表的索引进行排序。此变量指定的便是该缓冲区的大小。
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin = /var/log/mysql/master01 //开启binlog指定生成目录
binlog-do-db = afmweb_news //以下设置指定需要生成binlog的数据库，即需要数据恢复同步的库
binlog-do-db = gw_local
binlog-do-db = umc_ah
binlog-ignore-db = afmtimer //以下设置忽略生成binlog的数据库，一般系统库和不需要恢复同步的库
binlog-ignore-db = afmweb_news_cg
binlog-ignore-db = umc_ah_cg
binlog-ignore-db = gearman
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
relay-log=/var/log/mysql/relay_master01
log-error=/var/log/mysql/error.log
skip-name-resolve //重要参数，同一IDC ,IDC内部有DNS服务器，对各服务器的IP做了反向解析，但未对内网IP做反向解析，所以使用skip-name-resolve以后用内网地址向mysqlslap请求响应快了一半
# binary logging format - mixed recommended
binlog_format = ROW //注意此处严格限制binlog格式为ROW
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 140 //唯一，为了防止冲突一般需ip尾段
binlog-cache-size = 4M
max-binlog-size = 1G //单个binlog文件大小
#max-binlog-cache-size = 2G
sync-binlog = 1
log-slave-updates = true
gtid-mode = on //关键参数，on即开启主从模式为GTID模式，5.6版本以后新特性
slave_parallel_workers=4 //关键参数，新特性允许主从开启多线程，每个库单独一个线程
enforce-gtid-consistency = true //关键参数，和gtidmode必须一起打开，否则报错
master-info-repository = TABLE //relay.info明文存储不安全，把relay.info中的信息记录在table中相对安全。
relay-log-info-repository = TABLE
sync-master-info = 1 //控制从属服务器更新 master info 的间隔,1为实时记录
binlog-checksum = CRC32
master-verify-checksum = 1 //一下几个用于binlog日志验证
slave-sql-verify-checksum = 1
binlog-rows-query-log_events = 1
log-bin-trust-function-creators = 1
slow-query-log = 1 //慢日志相关参数
long-query-time = 5
slow-query-log-file = /var/log/mysql/slowquery.log
log-queries-not-using-indexes = 1
lower_case_table_names = 1
#slave-skip-errors = 1062,1053
#auto_increment_increment = 2
#auto_increment_offset = 1
#loose-innodb-trx=0
#loose-innodb-locks=0
#loose-innodb-lock-waits=0
#loose-innodb-cmp=0
#loose-innodb-cmp-per-index=0
#loose-innodb-cmp-per-index-reset=0
#loose-innodb-cmp-reset=0
#loose-innodb-cmpmem=0
#loose-innodb-cmpmem-reset=0
#loose-innodb-buffer-page=0
#loose-innodb-buffer-page-lru=0
#loose-innodb-buffer-pool-stats=0
#loose-innodb-metrics=0
#loose-innodb-ft-default-stopword=0
#loose-innodb-ft-inserted=0
#loose-innodb-ft-deleted=0
#loose-innodb-ft-being-deleted=0
#loose-innodb-ft-config=0
#loose-innodb-ft-index-cache=0
#loose-innodb-ft-index-table=0
#loose-innodb-sys-tables=0
#loose-innodb-sys-tablestats=0
#loose-innodb-sys-indexes=0
#loose-innodb-sys-columns=0
#loose-innodb-sys-fields=0
#loose-innodb-sys-foreign=0
#loose-innodb-sys-foreign-cols=0
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/var
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 2G //设置为内存的50-80%，根据RAM调整
innodb_additional_mem_pool_size = 128M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 500M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_file_per_table = 1
#other
character-set-server=utf8
collation-server=utf8_general_ci
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max-connect-errors=1000
max-connections=1200
transaction-isolation=read-committed
event_scheduler = 1
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
expire_logs_days = 10
- 修改从服务器slave:
复制master的my.cnf一份，只修改增加以下几个参数 server-id = 50 //修改
//以下需要添加指定主从的表和忽略主从的表
replicate_wild_do_table=afmweb_news.%
replicate_wild_do_table=gw_local.%
replicate_wild_do_table=umc_ah.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
- 重启两台服务器上的mysql
$ service mysql restart
- 在主服务器上建立帐户并授权slave:
$ mysql -u root -p
Enter password:
mysql> GRANT REPLICATION SLAVE ON *.* to 'dbslave'@'192.168.1.%' identified by 'yourpassword';
mysql> show master status\G
*************************** 1. row ***************************
File: master01.000017
Position: 477877
Binlog_Do_DB: afmweb_news,gw_local,umc_ah
Binlog_Ignore_DB: afmtimer,afmweb_news_cg,umc_ah_cg,gearman,mysql,information_schema,performance_schema
Executed_Gtid_Set: a9b9b63d-6b63-11e6-8f20-3440b5860519:1-13531
1 row in set (0.03 sec)
- 配置从服务器Slave：
$ mysql -u root -p
Enter password:
mysql> change master to master_host='192.168.1.140',master_user='dbslave',master_password='yourpassword',master_port=3306,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.09 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.140
Master_User: dbslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master01.000017
Read_Master_Log_Pos: 436422
Relay_Log_File: relay_master01.000016
Relay_Log_Pos: 436630
Relay_Master_Log_File: master01.000017
Slave_IO_Running: Yes //确保此处为Yes
Slave_SQL_Running: Yes //确保此处为Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: afmweb_news.%,gw_local.%,umc_ah.%
Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 436422
Relay_Log_Space: 437224
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 140
Master_UUID: a9b9b63d-6b63-11e6-8f20-3440b5860519
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: a9b9b63d-6b63-11e6-8f20-3440b5860519:5160-13531
Executed_Gtid_Set: a9b9b63d-6b63-11e6-8f20-3440b5860519:1-13531
Auto_Position: 1
1 row in set (0.00 sec)
- 主库创建数据库并还原数据
$ cd /opt/databak
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 207584
Server version: 5.6.23-log Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database afmweb_news; //创建需要还原的数据库，此处以afmweb_news为例
mysql> source afmweb_news.sql //导入还原数据，其它库类似
0 条评论