mysql5.6主从搭建的方法是什么

本篇内容介绍了“mysql5.6主从搭建的方法是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

系统：Centos6.6

主：192.168.142.129                                               Mysql-5.6.30.tar.gz

从：192.168.142.130           192.168.142.131            mysql-5.6.30.tar.gz

一、mysql主从复制原理

(1)    master将改变记录到二进制日志(binary log)中；

(2)    slave将master的binary log events拷贝到它的中继日志(relay log)；slave的I/O线程从master的二进制日志中读取事件并写入中继日志；

(3)    slave重做中继日志中的事件，将改变反映它自己的数据。slave的SQL线程从中继日志读取事件，并在本地重放其中的事件，使其与master中的数据一致。

mysql主从实现的步骤：

1、使用mysqldump 命令备份数据库，

2、查看主节点二进制的位置点

3、创建备份用户，并授权(replication client.replication slave)

4、从服务器修改server-id，必须与主mysql的server-id不同，开启中继日子，关闭二进制日子

5、从数据库，倒入数据，并使用授权用户，连接主mysql

6、start slave

SQL语言共分为以下几大类：查询语言DQL，控制语言DCL，操纵语言DML，定义语言DDL。事务控制TCL.

DQL（Data QUERY Languages）语句：即数据库定义语句，用来查询SELECT子句，FROM子句，WHERE子句组成的查询块，比如：select–from–where–grouop by–having–order by–limit

DDL（Data Definition Languages）语句：即数据库定义语句，用来创建数据库中的表、索引、视图、存储过程、触发器等，常用的语句关键字有CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。增删改表的结构

DML（Data Manipulation Language）语句：即数据操纵语句，用来查询、添加、更新、删除等，常用的语句关键字有：SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLaiN PLAN,LOCK TABLE,包括通用性的增删改查。增删改表的数据

DCL（Data Control Language）语句：即数据控制语句，用于授权/撤销数据库及其字段的权限（DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.）。常用的语句关键字有：GRANT,REVOKE。

TCL（Transaction Control Language）语句：事务控制语句，用于控制事务，常用的语句关键字有：COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。

二、mysql编译安装

#!/bin/bashyum -y install make GCc gcc-c++ openssl openssl-devel pcre-devel gd cmake ncurses ncurses-develid -u mysqlif [ `echo $?` -ne 0 ];thengroupadd mysqluseradd -M -g mysql -s /sbin/nologin mysqlfiif [ ! -d  "/usr/local/mysql" ];then    mkdir -p /usr/local/mysqlfimkdir -p /data/mysqlchown -R mysql:mysql /data/mysqlcd /home/soft/                          #软件存放目录tar zxvf mysql-5.6.30.tar.gzcd mysql-5.6.30cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DMYSQL_DATADIR=/data/mysql/data -DINSTALL_MANDIR=/usr/share/man -DMYSQL_tcp_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DEXTRA_CHARSETS=all -DDEFAULT_COLLATION=utf8_general_ci -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1make && make installchown -R mysql:mysql .chmod +x scripts/mysql_install_db./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysqlcp ./support-files/mysql.server  /etc/rc.d/init.d/mysqldchmod 755 /etc/init.d/mysqldchkconfig mysqld oncat> /etc/rc.d/init.d/mysqld <<'EOF'                #mysql启动脚本#!/bin/sh# Copyright Abandoned 1996 TCX DataKonsult AB & Monty Program KB & Detron HB# This file is public domain and comes with NO WARRANTY of any kind# MySQL daemon start/stop script.# Usually this is put in /etc/init.d (at least on Machines SYSV R4 based# systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.# When this is done the mysql server will be started when the machine is# started and shut down when the systems Goes down.# Comments to support chkconfig on RedHat linux# chkconfig: 2345 64 36# description: A very fast and reliable SQL database engine.# Comments to support LSB init script conventions### BEGIN INIT INFO# Provides: mysql# Required-Start: $local_fs $network $remote_fs# Should-Start: ypbind nscd ldap ntpd xntpd# Required-Stop: $local_fs $network $remote_fs# Default-Start:  2 3 4 5# Default-Stop: 0 1 6# Short-Description: start and stop MySQL# Description: MySQL is a very fast and reliable SQL database engine.### END INIT INFO# If you install MySQL on some other places than /usr/local/mysql, then you# have to do one of the following things for this script to work:## - Run this script from within the MySQL installation directory# - Create a /etc/my.cnf file with the following infORMation:#   [mysqld]#   basedir=<path-to-mysql-installation-directory># - Add the above to any other configuration file (for example ~/.my.ini)#   and copy my_print_defaults to /usr/bin# - Add the path to the mysql-installation-directory to the basedir variable#   below.## If you want to affect other MySQL variables, you should make your changes# in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.# If you change base dir, you must also change datadir. These may get# overwritten by settings in the MySQL configuration files.basedir=/usr/local/mysqldatadir=/data/mysql# Default value, in seconds, afterwhich the script should timeout waiting# for server start. # Value here is overriden by value in my.cnf. # 0 means don't wait at all# Negative numbers mean to wait indefinitelyservice_startup_timeout=900# Lock directory for RedHat / SuSE.lockdir='/var/lock/subsys'lock_file_path="$lockdir/mysql"# The following variables are only set for letting mysql.server find things.# Set some defaultsmysqld_pid_file_path=if test -z "$basedir"then  basedir=/usr/local/mysql  bindir=/usr/local/mysql/bin  if test -z "$datadir"  then    datadir=/data/mysql/data  fi  sbindir=/usr/local/mysql/bin  libexecdir=/usr/local/mysql/binelse bindir="$basedir/bin"  if test -z "$datadir"  then    datadir="$basedir/data"  fi  sbindir="$basedir/sbin"  libexecdir="$basedir/libexec"fi# datadir_set is used to determine if datadir was set (and so should be# *not* set inside of the --basedir= handler.)datadir_set=# Use LSB init script functions for printing messages, if possible#lsb_functions="/lib/lsb/init-functions"if test -f $lsb_functions ; then  . $lsb_functionselse  log_success_msg()  {    echo " SUCCESS! $@"  }  log_failure_msg()  {    echo " ERROR! $@"  }fiPATH="/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin"export PATHmode=$1    # start or stop[ $# -ge 1 ] && shiftother_args="$*"   # uncommon, but needed when called from an RPM upgrade action           # Expected: "--skip-networking --skip-grant-tables"           # They are not checked here, intentionally, as it is the resposibility           # of the "spec" file author to give correct arguments only.case `echo "testing\c"`,`echo -n testing` in    *c*,-n*) echo_n=   echo_c=     ;;    *c*,*)   echo_n=-n echo_c=     ;;    *)       echo_n=   echo_c='\c' ;;esacparse_server_arguments() {  for arg do    case "$arg" in      --basedir=*)  basedir=`echo "$arg" | sed -e 's/^[^=]*=//'`                    bindir="$basedir/bin"                    if test -z "$datadir_set"; then                      datadir="$basedir/data"                    fi                    sbindir="$basedir/sbin"                    libexecdir="$basedir/libexec"        ;;      --datadir=*)  datadir=`echo "$arg" | sed -e 's/^[^=]*=//'`                    datadir_set=1        ;;      --pid-file=*) mysqld_pid_file_path=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;      --service-startup-timeout=*) service_startup_timeout=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;    esac  done}wait_for_pid () {  verb="$1"           # created | removed  pid="$2"            # process ID of the program operating on the pid-filepid_file_path="$3" # path to the PID file.  i=0  avoid_race_condition="by checking again"  while test $i -ne $service_startup_timeout ; do    case "$verb" in      'created')        # wait for a PID-file to pop into existence.        test -s "$pid_file_path" && i='' && break        ;;      'removed')        # wait for this PID-file to disappear        test ! -s "$pid_file_path" && i='' && break        ;;      *)        echo "wait_for_pid () usage: wait_for_pid created|removed pid pid_file_path"        exit 1        ;;    esac    # if server isn't running, then pid-file will never be updated    if test -n "$pid"; then if kill -0 "$pid" 2>/dev/null; then        :  # the server still runs      else        # The server may have exited between the last pid-file check and now.          if test -n "$avoid_race_condition"; then          avoid_race_condition=""          continue  # Check again.                                                 fi        # there's nothing that will affect the file.        log_failure_msg "The server quit without updating PID file ($pid_file_path)."        return 1  # not waiting any more.      fi    fi    echo $echo_n ".$echo_c"    i=`expr $i + 1`    sleep 1  done  if test -z "$i" ; then    log_success_msg    return 0  else    log_failure_msg    return 1  fi}# Get arguments from the my.cnf file,# the only group, which is read from now on is [mysqld]if test -x ./bin/my_print_defaultsthen  print_defaults="./bin/my_print_defaults"elif test -x $bindir/my_print_defaultsthen  print_defaults="$bindir/my_print_defaults"elif test -x $bindir/mysql_print_defaultsthen  print_defaults="$bindir/mysql_print_defaults"else  # Try to find basedir in /etc/my.cnf  conf=/etc/my.cnf  print_defaults=  if test -r $conf  then    subpat='^[^=]*basedir[^=]*=\(.*\)$'    dirs=`sed -e "/$subpat/!d" -e 's//\1/' $conf`    for d in $dirs    do      d=`echo $d | sed -e 's/[  ]//g'`      if test -x "$d/bin/my_print_defaults"      then        print_defaults="$d/bin/my_print_defaults"        break      fi      if test -x "$d/bin/mysql_print_defaults"      then      print_defaults="$d/bin/mysql_print_defaults"        break      fi    done  fi  # Hope it's in the PATH ... but I doubt it  test -z "$print_defaults" && print_defaults="my_print_defaults"fi## Read defaults file from 'basedir'.   If there is no defaults file there# check if it's in the old (depricated) place (datadir) and read it from there#extra_args=""if test -r "$basedir/my.cnf"then  extra_args="-e $basedir/my.cnf"else  if test -r "$datadir/my.cnf"  then    extra_args="-e $datadir/my.cnf"  fifiparse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server`## Set pid file if not given#if test -z "$mysqld_pid_file_path"then  mysqld_pid_file_path=$datadir/`hostname`.pidelse  case "$mysqld_pid_file_path" in    /* ) ;;    * )  mysqld_pid_file_path="$datadir/$mysqld_pid_file_path" ;;  esacficase "$mode" in  'start')    # Start daemon    # Safeguard (relative paths, core dumps..)    cd $basedir    echo $echo_n "Starting MySQL"    if test -x $bindir/mysqld_safe    then      # Give extra arguments to mysqld with the my.cnf file. This script      # may be overwritten at next upgrade.      $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &      wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?      # Make lock for RedHat / SuSE      if test -w "$lockdir"      then        touch "$lock_file_path"      fi  exit $return_value    else      log_failure_msg "Couldn't find MySQL server ($bindir/mysqld_safe)"    fi    ;;  'stop')    # Stop daemon. We use a signal here to avoid having to know the    # root passWord.    if test -s "$mysqld_pid_file_path"    then      mysqld_pid=`cat "$mysqld_pid_file_path"`      if (kill -0 $mysqld_pid 2>/dev/null)      then        echo $echo_n "Shutting down MySQL"        kill $mysqld_pid        # mysqld should remove the pid file when it exits, so wait for it.        wait_for_pid removed "$mysqld_pid" "$mysqld_pid_file_path"; return_value=$?      else        log_failure_msg "MySQL server process #$mysqld_pid is not running!"        rm "$mysqld_pid_file_path"      fi      # Delete lock for RedHat / SuSE      if test -f "$lock_file_path"      then        rm -f "$lock_file_path"      fi      exit $return_value else      log_failure_msg "MySQL server PID file could not be found!"    fi    ;;  'restart')    # Stop the service and regardless of whether it was    # running or not, start it again.    if $0 stop  $other_args; then      $0 start $other_args    else      log_failure_msg "Failed to stop running server, so refusing to try to start."      exit 1    fi    ;;  'reload'|'force-reload')    if test -s "$mysqld_pid_file_path" ; then      read mysqld_pid <  "$mysqld_pid_file_path"      kill -HUP $mysqld_pid && log_success_msg "Reloading service MySQL"      touch "$mysqld_pid_file_path"    else      log_failure_msg "MySQL PID file could not be found!"      exit 1    fi    ;;  'status')    # First, check to see if pid file exists    if test -s "$mysqld_pid_file_path" ; then      read mysqld_pid < "$mysqld_pid_file_path"      if kill -0 $mysqld_pid 2>/dev/null ; then  log_success_msg "MySQL running ($mysqld_pid)"        exit 0      else        log_failure_msg "MySQL is not running, but PID file exists"        exit 1      fi    else      # Try to find appropriate mysqld process      mysqld_pid=`pidof $libexecdir/mysqld`      if test -z $mysqld_pid ; then        if test -f "$lock_file_path" ; then          log_failure_msg "MySQL is not running, but lock file ($lock_file_path) exists"          exit 2        fi        log_failure_msg "MySQL is not running"        exit 3      else        log_failure_msg "MySQL is running but PID file could not be found"        exit 4      fi    fi    ;;    *)      # usage      basename=`basename "$0"`      echo "Usage: $basename  {start|stop|restart|reload|force-reload|status}  [ MySQL server options ]"      exit 1    ;;                             esacexit 0EOFcat> /etc/my.cnf <<'EOF'                    #mysql配置文件[client]#password       = your_passwordport            = 3306Socket          = /tmp/mysql.sock# Here follows entries for some specific programs# The MySQL server[mysqld]port            = 3306socket          = /tmp/mysql.sockskip-external-lockingkey_buffer_size = 384Mmax_allowed_packet = 64Mtable_open_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size = 32M# Try number of CPU's*2 for thread_concurrencythread_concurrency = 8basedir = /usr/local/mysqldatadir = /data/mysqlmax_connections = 5000long_query_time = 1slow_query_log = 1slow_query_log_file = /var/log/mysql/slow.log# 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!lower_case_table_names = 1# Replication Master Server (default)# binary logging is required for replication#log-bin=mysql-binskip-name-resolve# 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      = 1# binary logging - not required for slaves, but recommended#log-bin=mysql-bin## binary logging format - mixed recommended #binlog_format=mixed# Uncomment the following if you are using InnoDB tables#innodb_data_home_dir = /data/mysql/data#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend#innodb_log_group_home_dir = /data/mysql/data# You can set .._buffer_pool_size up to 50 - 80 %# of RAM but beware of setting memory usage too highinnodb_buffer_pool_size = 4096M#innodb_additional_mem_pool_size = 20M# Set .._log_file_size to 25 % of buffer pool sizeinnodb_log_file_size = 512M#innodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 0#innodb_lock_wait_timeout = 50[mysqldump]quickmax_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 = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeoutEOFln -s /usr/local/mysql/bin/mysqladmin /usr/binln -s /usr/local/mysql/lib/mysql /usr/libln -s /usr/local/mysql/include/mysql /usr/include/mysqlmkdir /var/lib/mysqlln -s /tmp/mysql.sock /var/lib/mysql/mysql.sockecho 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profilesleep 2source /etc/profileservice mysqld startsleep 5cd /usr/local/mysql/bin && mysqladmin -uroot password 'mysql'    #授权root用户的passwordsource /etc/profile

三、主从配置

1、这里验证主库有数据的情况，然后授权有复制权限的用户

mysql> create database db1;mysql> use db1mysql> create table t1(id int, name varchar(12));mysql> insert into t1 values(1, 'tom'), (2, 'jerry'), (3, 'jack');mysql> grant replication slave,replication client  on *.* to 'backuser'@'192.168.142.130' identified by 'mysqll';mysql> grant replication slave,replication client  on *.* to 'backuser'@'192.168.142.131' identified by 'mysql';mysql> flush privileges;

2、修改各个数据库的配置文件后重启数据库

vi /etc/my.cnf        #主库配置文件server-id=1log-bin=mysql-binbinlog-do-db=db1binlog-ignore-db=mysql vi /etc/my.cnf       #从库配置文件server-id=2           #从库id不能和主库一样，其他从库往后面排log-bin=relay-binreplicate-do-db=db1        #同步db1库replicate-ignore-db=mysql  #不会同步mysql库read_only                  #只读service mysqld restart

3、主库锁表备份，然后文件传给从库

mysql> flush tables with read lock;     #主库锁表防止新的数据写入mysql> show master status;              #查看主库位置节点新打开一个终端备份：mysqldump -u root -p --default-character-set=utf8 --opt -Q -R --skip-lock-tables db1 > /root/db1.sql  scp /root/db1.sql root@192.168.142.130:/root   scp /root/db1.sql root@192.168.142.130:/root

4、从库导入数据，然后change到主库的节点

mysql -u root -pmysql> create database db1;mysql> use db1mysql> source /root/db1.sqlmysql> change master to master_host='192.168.142.129',master_user='backuser',master_password='mysql',master_log_file='mysql-bin.000001',master_log_pos=120;mysql> start slave;mysql> show slave status\G

5、主库解锁

mysql> unlock tables;

以上配置对主从不同步，重新配置主从同样适用。

四、主从不同步

1、造成不同步的原因

网络的延迟主从两台机器的负载不一致max_allowed_packet设置不一致key自增键开始的键值跟自增步长设置不一致引起的主从不一致mysql异常宕机情况下，如果未设置sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能

出现binlog或者relaylog文件出现损坏，导致主从不一致mysql本身的bug引起的主从不同步版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面不支持该功能

2、解决办法

（1）忽略错误后，继续同步

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况

stop slave; set global sql_slave_skip_counter =1; start slave; show slave status\G

（2）重新做主从

参考上面配置主库锁表重新做主从。

“mysql5.6主从搭建的方法是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注编程网网站，小编将为大家输出更多高质量的实用文章！