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

ゝ一纸荒年。 2022-08-21 04:18 140阅读 0赞

系统基础

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;  
====================================================================================================================================