一 前戏

#安装yum install -y mysql-server mysql mysql-devel #不同实例目录mkdir /data/{3306,3307} -p   # 授权chown -R mysql.mysql /data 

 

 

二 配置文件与启动

#删除或者重命名原/etc/my.cnf配置文件 

mv /etc/my.cnf /etc/my.cnf.bak#创建配置文件 vi /data/3306/my.cnf （以下为配置文件内容）# 配置文件从这里开始[client]port      = 3306socket    = /data/3306/mysql.sock[mysql]no-auto-rehash[mysqld]user    = mysqlport    = 3306socket  = /data/3306/mysql.sockbasedir = /usr   #编译和二进制安装按实际目录填写路径（/usr为yum安装的）datadir = /data/3306/dataopen_files_limit    = 1024back_log = 600max_connections = 800max_connect_errors = 3000table_cache = 614external-locking = FALSEmax_allowed_packet =8Msort_buffer_size = 1Mjoin_buffer_size = 1Mthread_cache_size = 100thread_concurrency = 2query_cache_size = 2Mquery_cache_limit = 1Mquery_cache_min_res_unit = 2kthread_stack = 192Ktmp_table_size = 2Mmax_heap_table_size = 2Mlong_query_time = 1pid-file = /data/3306/mysql.pidlog-bin = /data/3306/mysql-bin  #主从同步的关键点，从库上不需要开启relay-log = /data/3306/relay-binrelay-log-info-file = /data/3306/relay-log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mexpire_logs_days = 7key_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_insert_buffer_size = 1Mlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032,1062replicate-ignore-db=mysqlserver-id = 1    #主库从库ID 不可相同[mysqldump]quickmax_allowed_packet = 2M[mysqld_safe]log-error=/data/3306/mysql3306.errpid-file=/data/3306/mysqld.pid# 配置文件到这里结束

=========================================

3 创建启动脚本 vi /data/3306/mysql

#启动脚本从这里开始#!/bin/shport=3306user="root"pwd="123456"    #按实际密码填写Path="/usr/bin"    #按实际安装指定mysql可执行程序的路径（可用which mysql查找）sock="/data/${port}/mysql.sock"start_mysql(){     if [ ! -e "$sock" ];then      printf "Starting MySQL...\n"      /bin/sh ${Path}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &    else      printf "MySQL is running...\n"      exit    fi}stop_mysql(){     if [ ! -e "$sock" ];then       printf "MySQL is stopped...\n"       exit    else       printf "Stoping MySQL...\n"       ${Path}/mysqladmin -u ${user} -p${pwd} -S /data/${port}/mysql.sock shutdown   fi}restart_mysql(){     printf "Restarting MySQL...\n"    stop_mysql    sleep 2    start_mysql}case $1 instart)    start_mysql;;stop)    stop_mysql;;restart)    restart_mysql;;*)    printf "Usage: /data/${port}/mysql {start|stop|restart}\n"esac#启动脚本到此结束

chmod +x /data/3306/mysql  #授权可执行

===============================================================

4 为3307也创建一样的一份，注意把所有3306替换3307  修改不同的server-id

  cp /data/3306/mysql /data/3307/  cp /data/3306/my.cnf /data/3307/  sed -i 's/server-id = 1/server-id = 2/g' /data/3307/my.cnf  sed -i 's/3306/3307/g' /data/3307/my.cnf  sed -i 's/3306/3307/g' /data/3307/mysql 

 

 

三 初始化2个实例

#初始化3306端口数据库mysql_install_db --datadir=/data/3306/data --user=mysql#启动3306数据库/data/3306/mysql start#修改3306实例登录密码mysqladmin -uroot password '123456' -S /data/3306/mysql.sock mysql_install_db --datadir=/data/3307/data --user=mysql/data/3307/mysql startmysqladmin -uroot password '123456' -S /data/3307/mysql.sock

 

四 配置主库#登录3306实例（主库）mysql -uroot -p123456 -S /data/3306/mysql.sock#授权同步的帐号密码grant replication slave on *.* to identified by'66888888';#刷新权限flush privileges;#退出exit #主库锁表mysql -uroot -p123456 -S /data/3306/mysql.sock -e "flush tables with read lock;"#查看主库binlog状态mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show master status;" >/mysql.log#备份主库mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B |gzip >/mysql.sql.gz#主库解锁mysql -uroot -p123456 -S /data/3306/mysql.sock -e "unlock tables;"备注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

 

 

五 配置从库

#导入主库的备份gzip -d /mysql.sql.gzmysql -uroot -p'123456' -S /data/3307/mysql.sock < /mysql.sqlmysql -uroot -p'123456' -S /data/3307/mysql.sock#设置同步信息mysql> CHANGE MASTER TO    -> MASTER_HOST='192.168.1.7',       #服务器IP    -> MASTER_PORT=3306,                #主库端口    -> MASTER_USER='rep',               #同步的用户    -> MASTER_PASSWORD='66888888',      #同步的用户密码    -> MASTER_LOG_FILE='mysql-bin.000002',  #binlog文件（这个信息在/mysql.log里哦）    -> MASTER_LOG_POS=424;                  #位置点（这个信息在/mysql.log里哦）

备注：可以一次设置以上信息change master to master_host='192.168.145.222',master_user='mysync',master_password='q123456',

         master_log_file='mysql-bin.000004',master_log_pos=308;#开启从模式mysql> start slave;            #查看状态mysql -uroot -p'123456' -S /data/3307/mysql.sock -e "show slave status\G"|egrep "Seconds_Behind_Master|_Running"#2个Yes表示成功，如不成功最多可能是上一步帐号同步设置可能有问题。