mysql主从配置安装_mysql主从配置完整版（二进制安装）

系统基础

centos6.5

mysql5.6 防火墙关闭，目录挂载等 fdisk /dev/sdb vgcreate datavg /dev/sdb1 lvcreate -L 99G -n datalv datavg mkfs.ext4 /dev/datavg/datalv mkdir /data mount /dev/datavg/datalv /data/ echo "/dev/datavg/datalv /data/ ext4 defaults 0 0" >>/etc/fstab mkdir /data/mysqldata ip规划 10.249.1.99 master 10.249.1.97 slave 安装脚本脚本编辑(自己拿去测试一下) softdir="/data/soft" installdir="/usr/local" datadir="/data/mysqldata" USER=`whoami` if [ "$USER" != 'root' ] ;then    echo "Please use root user,Thanks"    exit fi MYSQL_USER=`egrep -i mysql /etc/passwd` if [ -z "$MYSQL_USER" ] ;then        useradd -r -s /sbin/nologin mysql    else        echo "user mysql was added" fi #grep mysql /etc/passwd || groupadd -g 27 mysql #grep mysql /etc/passwd || useradd -M -s /sbin/nologin -u 27 -g 27 mysql yum install -y vim screen lsof gzip unzip tree ntp curl wget libaio* cd $softdir wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz tar -xvf mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz ln -s $softdir/mysql-5.6.28-linux-glibc2.5-x86_64 /usr/local/mysql cd /usr/local/mysql/ chown -R mysql.mysql /usr/local/mysql/ cd support-files cp mysql.server /etc/init.d/mysqld chmod 755 /etc/init.d/mysqld chkconfig --add mysqld chkconfig --level 35 mysqld on /usr/local/mysql/scripts/mysql_install_db  --user=mysql --basedir=$installdir --datadir=$datadir if [ $? -ne 0 ];then         exit 1         echo "mysql_install_db ERROR" fi mv /etc/my.cnf /etc/my.cnf_bak cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf sed -i 's/^# basedir = ...../basedir = \/usr\/local\/mysql/g' /etc/my.cnf sed -i 's/^# datadir = ...../datadir = \/data\/mysqldata/' /etc/my.cnf sed -i 's/^# port = ...../port = 3306/' /etc/my.cnf sed -i 's/^# socket = ...../socket = \/tmp\/mysql.sock/' /etc/my.cnf sed -i 's/^# server_id = ...../server_id = 1002/' /etc/my.cnf /etc/init.d/mysqld start echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile source /etc/profile PROC=`ps aux|grep mysql |grep -v grep -c` PORT=`netstat -lnp|grep 3306` if [ ! -z "$PORT" ] && [ "$PROC" -ne 0 ] ;then    echo "mysql install and started secussefull" fi echo echo echo echo "Show more options in  http://dev.mysql.com/doc/refman/5.6/en/source-configuration-options.html" echo "my.cnf is in /etc/my.cnf" echo "Run /etc/init.d/mysqld start|restart|status" =================================================================================================================== 主从配置 sed -i 's/^# log_bin/log_bin = \mysql_bin.log/' /etc/my.cnf 设置密码  /usr/local/mysql/bin/mysqladmin -u root password '123' 或  /usr/local/mysql/bin/mysqladmin -u root -h mysql1 password 'new-password'   删除空用户 登陆MySQL并且删除空用户 mysql> SELECT user,host,password FROM mysql.user; +------+-----------+----------+ | user | host      | password | +------+-----------+----------+ | root | localhost |          | | root | 10-4-5-9  |          | | root | 127.0.0.1 |          | | root | ::1       |          | |      | localhost |          | |      | 10-4-5-9  |          | +------+-----------+----------+ 6 rows in set (0.00 sec) mysql> UPDATE mysql.user set password = PASSWORD('123') WHERE user = 'root'; mysql> DROP USER ''@localhost; mysql> FLUSH PRIVILEGES; 主节点 grant replication slave on *.* to 'wolf'@'10.249.1.97' identified by 'wolf'; flush privileges; #mysql -uroot -p123 -e "grant replication slave on *.* to 'wolf'@'10.249.1.97' identified by 'wolf';" #mysql -uroot -p123 -e "flush privileges;" show master status; mysql> show master status     -> ; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql_bin.000002 |      407 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 从节点 change master to master_host='10.249.1.99',master_user='wolf',master_password='wolf',master_log_file='mysql_bin.000002', master_log_pos=407; start slave; show slave status\G; ==================================================================================================================================== 测试 mysqlbinlog  /mysql/data/mysql-bin.000002 mysql> use mysql； mysql> create database hehe; mysql> show databases; mysql> show tables; mysql> use hehe mysql> show tables; mysql> create table bbb (id int ,name varchar(20) ); mysql> show tables; mysql> insert into bbb id,name values(1,'123'); mysql> show master status; mysql> select * from bbb; mysql> create database lang; 从库上查看 show create table bbb; ====================================================================================================================================