现在我们公司的MySQL服务器都是单实列服务器,一旦出现宕机等事与愿违的事,会影响用户体验,数据丢失等。
故而使用MySQL主从复制技术解决*数据分布*负载均衡*备份*高可用性*主从切换等
mysql主从(主主)安装配置文档
关闭防火墙,关闭selinux,配置阿里yum原。
RPM:
1,下载rpm包
中国科学技术大学开源软件镜像 http://mirrors.ustc.edu.cn/
wget http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-8.0/mysql-community-client-8.0.23-1.el7.x86_64.rpm
wget http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-8.0/mysql-community-server-8.0.23-1.el7.x86_64.rpm
wget http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-8.0/mysql-community-common-8.0.23-1.el7.x86_64.rpm
wget http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-8.0/mysql-community-libs-8.0.23-1.el7.x86_64.rpm
2,安装三个相关软件
yum install -y net-tools.x86_64 libaio.x86_64 perl.x86_64
3,安装MySQL服务器
yum install -y mysql-community*
yum remove -y mariadb-libs.x86_64(出现错误解决办法)yum install -y mysql-community*
4,启动MySQL服务器并设置开机启动
systemctl start mysqld
systemctl enable mysqld
5,查询并修改MySQL密码
grep 'password' /var/log/mysqld.log (最后一行:后面全部是密码)mysqladmin -uroot -p'查到的密码放在这里' password '设置一个新密码‘
6,登录MySQL程序
mysql -uroot -p"liuyijing@666"
show databases; 查询到默认数据库,安装完成。
源码包:
1,准备编译环境
yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make cmake
wget http://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz
2,准备源码包
groupadd mysqluseradd -r -g mysql -s /bin/false mysql
tar xvf mysql-8.0.23.tar.gz (网上自己找源码包下载,然后解压)
cd mysql-8.0.23 mv ../boost_1_59_0.tar.gz .tar xf boost_1_59_0.tar.gz
3,配置
cmake . \
-DWITH_BOOST=boost_1_59_0/ \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DSYSCONFDIR=/etc \
-DMYSQL_DATADIR=/usr/local/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
4,编译,安装
make 等待10分钟左右 make install
5,初始化
cd /usr/local/mysqlmkdir mysql-files
chown -R mysql.mysql /usr/local/mysql
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 记下产生的密码
vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
6,开机启动MySQL
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqldchkconfig mysqld on
service mysqld start
/usr/local/mysql/bin/mysql -u root -p'a/3wid2>l:rc' 进入数据库
/usr/local/mysql/bin/mysqladmin -u root -p '老密码' password '新密码' 配置新密码
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile 设置环境变量
source /etc/profile 刷新环境变量密码忘记:
vim /etc/my.cnf
skip_grant_tables
直接MySQL就可以登录了
update mysql.user set authentication_string=password('123qqq...A') where user='root' and host='localhost';
直接修改密码 然后去掉添加的那一条 就可以登录了
grant all on bbs.* to phptest@'192.168.184.%' identified by '123456'; 针对某个库赋予权限。
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this st
alter user 'root'@'localhost' identified by '123456';
两种安装方式,测试选择RPM 上线选择源码
主从:
主:
1,修改配置文件,开启二进制日志
vim /etc/my.cnf
log_binserver-id=1
gtid_mode=ON
enforce_gtid_consistency=1
systemctl restart mysqld
2,创建复制用户
进入MySQL
grant replication slave, replication client on *.* to 'rep'@192.168.184.%' identified by 'liuyijing@666';
flush privileges;
3,备份数据库数据
退出MySQL
mysqldump -p'liuyijing@666' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql
scp -r mysql-all.sql 192.168.184.145:/tmp
从:
1,修改配置文件,开启二进制日志
vim /etc/my.cnf
log_binserver-id=2
gtid_mode=ON
enforce_gtid_consistency=1
systemctl restart mysqld
2,同步数据
登录MySQL
set sql_log_bin=0;source /tmp/mysql-all.sql
set sql_log_bin=1;
3,设置主服务器
change master to
master_host='192.168.184.129',
master_user='tep',
master_password='liuyijing@666',
master_auto_position=1;
start slave;
4,查看状态
返回主服务器更新数据,在从服务器观察是否同步。
show slave status\G; 查看主从状态
5,全部添加远程权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'liuyijing@666' WITH GRANT OPTION;
FLUSH PRIVILEGES;
设置多从只需要把从的配置再配置一遍即可
设置双主只需要把从的设置放在主的服务器指向从即可