mysql 主从

#mysql 主从
#切换到root
cd /usr/local/
mkdir mysql
cd mysql
#检查旧版本
yum list installed | grep mysql
#如有，删除旧版本
yum -y remove xxx

wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz
tar zvxf mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz

mv -f  mysql-5.7.13-linux-glibc2.5-x86_64/* ./
rm -rf mysql-5.7.13-linux-glibc2.5-x86_64
mkdir data

groupadd mysql
#-r参数表示mysql用户是一个系统用户，不能登录
useradd -r -g mysql mysql


chown mysql:mysql -R /usr/local/mysql

cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
vi /etc/my.cnf

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
character_set_server=utf8

#初始化,读取日志最后一样root密码：kD4/e<3IGKR!
/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql



chown mysql:mysql -R /etc/my.cnf

#后台启动
/usr/local/mysql/bin/mysqld_safe &
#登录
/usr/local/mysql/bin/mysql -u root -p
#更改密码
SET PASSWORD = PASSWORD('passwd');

#远程登录授权
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'pssswd' WITH GRANT OPTION;
FLUSH PRIVILEGES;
quit

#创建服务
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
#开机启动
chkconfig --add mysql

#启动
service mysql start
#状态
service mysql status
#关闭
service mysql stop
#重启
service mysql restart

#集群配置
vi /etc/my.cnf
#[master必须]启用二进制日志  
log-bin=mysql-bin
#[必须]服务器唯一ID，默认是1，一般取IP最后一段  
server-id=50
#master创建复制帐号
/usr/local/mysql/bin/mysql -u root -p
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'xxx'@'192.168.1.6' IDENTIFIED BY 'xxxx';

#查看主服务器状态
show master status\G;

#slave 启动复制
/usr/local/mysql/bin/mysql -u root -p
#配置从服务复制，master_log_file=查询master(主服务器)的状态得到的File列的值，Position列的值
change master to master_host='192.168.1.5',master_user='xxx',master_password='passwd',master_log_file='mysql-bin.000001',master_log_pos=463; start slave;
#查看从服务器状态，Slave_IO_Running: Yes  ,lave_SQL_Running: Yes
show slave status\G;

#测试
#主
create database db_test_slave;  
use db_test_slave;  
create table tb_test(id int(3), name varchar(50));  
insert into tb_test values(1,'hello slave');  
show databases;
#从
show databases;
use db_test_slave;
select * from tb_test;

#运维
#主复制关闭
stop slave;
#主复制启动
start slave;

#其他
show VARIABLES like '%max_allowed_packet%';
#修改最大写入数据大小
vi /etc/my.cnf

max_allowed_packet = 20M

#数据库引擎查询
show engines;