一、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/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
三、主从配置
1、这里验证主库有数据的情况，然后授权有复制权限的用户
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;
2、修改各个数据库的配置文件后重启数据库
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
数据库
发布于：2022-01-20 10:53
|
阅读数：304
|
评论：0
QQ好友和群
QQ空间
