MYSQL-主从配置

1、依赖安装

yum install -y cmake gcc gcc-c++ ncurses-devel

2、服务安装

#1、解压安装包
tar -xf mysql-5.7.16.tar.gz
tar -xf boost_1_59_0.tar.gz (支持json)
mkdir /usr/local/mysql
mv boost_1_59_0 /usr/local/mysql/boost
#2、创建目录和用户
groupadd mysql && useradd -g mysql mysql -s /bin/false
mkdir /data/mysql/data -p && chown mysql.mysql /data/mysql/data
mkdir /logs/mysql -p && chown mysql.mysql /logs/mysql/
mkdir /data/binlog/ && chown mysql.mysql /data/binlog/
#3、开始编译安装
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql/data -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysqld.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DMYSQL_USER=mysql -DWITH_BOOST=/usr/local/mysql/boost
make && make install
#4、初始化数据库
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql/data
cp support-files/mysql.server /etc/init.d/mysqld && chmod +x /etc/init.d/mysqld
#5、设置BIN环境
/etc/profile
PATH=$PATH:/usr/local/mysql/bin/:/usr/local/mysql/lib/
export PATH

3.修改主库配置文件

[client]
#password = your_password
default-character-set = utf8
port = 3306
socket = /usr/local/mysql/run/mysqld.sock
[mysqld]
port = 3306
socket = /usr/local/mysql/run/mysqld.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 5M
table_open_cache = 512
#table_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 300
query_cache_size = 32M
query_cache_limit= 4M
join_buffer_size = 8M
basedir = /usr/local/mysql
datadir = /data/mysql/data
pid-file = /usr/local/mysql/run/mysqld.pid
log-error = /logs/mysql/mysqld-error.log
slow_query_log
slow_query_log_file = /logs/mysql/mysql-slow-query.log
long_query_time = 1
wait_timeout = 28800
interactive_timeout = 28800
#table_cache = 614
max_connections = 5000
max_connect_errors = 6000
tmp_table_size = 256M
key_buffer_size = 2048M
bulk_insert_buffer_size = 64M
#myisam_sort_buffer_size = 128M
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_buffer_pool_size = 8192M
innodb_thread_concurrency = 8
innodb_log_files_in_group = 2
innodb_file_per_table = 1
long_query_time = 1
secure_file_priv = /tmp
#####主从配置项开始
#主库配置
bind-address = master_ip
log-bin = /data/binlog/mysql-binlog
log-bin = mysql-bin
binlog_format = mixed
server-id = 131
expire_logs_days = 10 #只保留7天的二进制日志，以防磁盘被日志占满
replicate-do-db = test #需要做复制的数据库名；这里不设置只配置备份的数据库
binlog-ignore-db = mysql #不备份的数据库
binlog-ignore-db = information_schema
binlog-ignore-db = performation_schema
binlog-ignore-db = sys
binlog-do-db=test #需要做复制的数据库名

4.从库配置

#从库配置
bind-address = slave_ip
#log-bin = /data/binlog/mysql-binlog
log-bin = mysql-bin
binlog_format = mixed
expire_logs_days = 10
server-id = 100
binlog-ignore-db = mysql #不备份的数据库
binlog-ignore-db = information_schema
binlog-ignore-db = performation_schema
binlog-ignore-db = sys
#replicate-do-db = db_name #需要同步的数据库
#replicate-ignore-db = mysql #不需要同步的库
#replicate-ignore-db = test #一般将mysql与test这两个库取消不要同步，保证业务使用的数据库即可

  [mysqldump]
  quick
  max_allowed_packet = 16M
  [mysql]
  no-auto-rehash
  [myisamchk]
  key_buffer_size = 256M
  sort_buffer_size = 256M
  read_buffer = 2M
  write_buffer = 2M
  [mysqlhotcopy]
  interactive-timeout

5、启动和验证服务

#1、启动从库
/etc/init.d/mysqld start
修改初始密码
SET PASSWORD = PASSWORD('123456');
#########主库相关操作
#2、配置主从
##1)修改my.cnf配置
##2)重启主库
/etc/init.d/mysqld restart
##3)创建同步用户
create user repl;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'Repuser2018';
##4)设置主库为只读(确保数据的一致性)
FLUSH TABLES WITH READ LOCK;
##5)查看主库状态(记录file和position的值)
SHOW MASTER STATUS;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1024 | | | |

  ##6)导出主库数据
  单个库导出
  mysqldump -uroot -p --master-data --single-transaction -R --triggers -B youdb > youdb.sql
  全库导出
  mysqldump -uroot -p --master-data --single-transaction -R --triggers -A > all.sql
  ##7)数据导出完成后释放只读锁,让主库重新恢复写操作
  UNLOCK TABLES; 

  #########从库相关操作
  ##1)导入数据

  mysql -u root -p < all.sql 

  ##2)与主库建立连接，同步数据
  STOP SLAVE;
  change master to master_host='yourip',master_port=master_port,master_user='repl',master_password='Repuser2018',master_log_file='bin_file_name',master_log_pos=pos_value;
  START SLAVE;
  ##3)查看从库状态
  SHOW SLAVE STATUS \G;
  ############相关命令
  1、创建用户和授权
  CREATE USER 'youuser'@'%' IDENTIFIED BY '123456';
  grant select, insert, update, delete ON youdb.* TO youuser@'%' IDENTIFIED BY "123456";
  GRANT ALL ON yourdb.* to ddbshuser@'yourip' IDENTIFIED BY 'yourpass';
  flush privileges;

6.MySQL 复制的基本过程

# MySQL 复制的基本过程：
#1、Slave 上面的IO线程连接上 Master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容；
#2、Master 接收到来自 Slave 的 IO 线程的请求后，通过负责复制的 IO 线程根据请求信息读取指定日志指定位置之后的日志信息，返回给 Slave 端的 IO 线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 Binary Log 中的位置；
#3、Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master- info文件中，以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”
#4、Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后，会马上解析该 Log 文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句，并在自身执行这些 Query。这样，实际上就是在 Master 端和 Slave 端执行了同样的 Query，所以两端的数据是完全一样的。
# mysql bin log的设置
##MASTER：
master上只需要打开bin log；
设置binlog的复制模式为混合模式，即binlog_format=mixed；
不需要打开bin-relay。
##SLAVE：
不需要打开bin-log，因为SLAVE上的bin log都来源于MASTER的数据传入，如果打开，这里其实是给slave增加了IO负担；
上面的复制过程可以看到，我们slave上的IO进程获得到MASTER上的bin-log日志，然后记录到中继日志relay-bin中，然后slave上的SQL进程读取relay-bin的内容，并在slave上执行，达到复制的目的。
此处只要开启relay-bin就可以了。
如果有想使用SLAVE作为MASTER的备机，随时转换位置，作为MASTER使用的话，建议还是要再SLAVE上开启binlog

7.问题处理

#问题描述：
从库出现卡住
#解决办法：
STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=900000; START SLAVE;