[MySQL] mysql5.6主从搭建以及不同步问题详解

• 一、mysql主从复制原理
  
• 二、mysql编译安装
  
• 三、主从配置
  
• 四、主从不同步
  

#!/bin/bash
yum -y install make gcc gcc-c++ openssl openssl-devel pcre-devel gd cmake ncurses ncurses-devel
id -u mysql
if [ `echo $?` -ne 0 ];
then
groupadd mysql
useradd -m -g mysql -s /sbin/nologin mysql
fi
 
if [ ! -d  "/usr/local/mysql" ];
then
  mkdir -p /usr/local/mysql
fi
mkdir -p /data/mysql
chown -r mysql:mysql /data/mysql
cd /home/soft/              #软件存放目录
tar zxvf mysql-5.6.30.tar.gz
cd mysql-5.6.30
 
cmake -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=1
make && make install
 
chown -r mysql:mysql .
chmod +x scripts/mysql_install_db
 
./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql
cp ./support-files/mysql.server  /etc/rc.d/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig mysqld on
 
cat> /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/mysql
datadir=/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 indefinitely
service_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 defaults
mysqld_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/bin
else
 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_functions
else
  log_success_msg()
  {
  echo " success! $@"
  }
  log_failure_msg()
  {
  echo " error! $@"
  }
fi
 
path="/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin"
export path
mode=$1  # start or stop
 
[ $# -ge 1 ] && shift
 
 
other_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' ;;
esac
 
parse_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-file
pid_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_defaults
then
  print_defaults="./bin/my_print_defaults"
elif test -x $bindir/my_print_defaults
then
  print_defaults="$bindir/my_print_defaults"
elif test -x $bindir/mysql_print_defaults
then
  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"
  fi
fi
 
parse_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`.pid
else
  case "$mysqld_pid_file_path" in
  /* ) ;;
  * )  mysqld_pid_file_path="$datadir/$mysqld_pid_file_path" ;;
  esac
fi
 
case "$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
  ;;
               
esac
 
exit 0
eof
 
cat> /etc/my.cnf <<'eof'          #mysql配置文件
[client]
#password     = your_password
port      = 3306
socket      = /tmp/mysql.sock
 
# here follows entries for some specific programs
 
# the mysql server
[mysqld]
port      = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384m
max_allowed_packet = 64m
table_open_cache = 512
sort_buffer_size = 2m
read_buffer_size = 2m
read_rnd_buffer_size = 8m
myisam_sort_buffer_size = 64m
thread_cache_size = 8
query_cache_size = 32m
# try number of cpu's*2 for thread_concurrency
thread_concurrency = 8
basedir = /usr/local/mysql
datadir = /data/mysql
max_connections = 5000
long_query_time = 1
slow_query_log = 1
slow_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-bin
skip-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 high
innodb_buffer_pool_size = 4096m
#innodb_additional_mem_pool_size = 20m
# set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 512m
#innodb_log_buffer_size = 8m
innodb_flush_log_at_trx_commit = 0
#innodb_lock_wait_timeout = 50
 
[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 = 256m
sort_buffer_size = 256m
read_buffer = 2m
write_buffer = 2m
 
[mysqlhotcopy]
interactive-timeout
 
eof
 
ln -s /usr/local/mysql/bin/mysqladmin /usr/bin
ln -s /usr/local/mysql/lib/mysql /usr/lib
ln -s /usr/local/mysql/include/mysql /usr/include/mysql
mkdir /var/lib/mysql
ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock
echo 'export path=$path:/usr/local/mysql/bin' >> /etc/profile
sleep 2
source /etc/profile
 
service mysqld start
 
sleep 5
cd /usr/local/mysql/bin && mysqladmin -uroot password 'mysql'  #授权root用户的password
source /etc/profile

mysql> create database db1;
mysql> use db1
mysql> 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;

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

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

mysql -u root -p
mysql> create database db1;
mysql> use db1
mysql> source /root/db1.sql
mysql> 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

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