mysql主主配置

#安装服务端
yum -y install mysql-server
#配置文件目录
mkdir /opt/safeweb/mysql
cd /opt/safeweb/mysql
mkdir /opt/safeweb/log
chown mysql:mysql -R mysql
chown mysql:mysql -R log

#修改配置文件
vi /etc/mysql.conf
datadir=/opt/safeweb/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0
log-error=/opt/safeweb/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#可以为任意自然数，必须保证两台mysql主机不重复
[mysqld]
server-id=13
#启动mysql二进制日志，如果没有配置这个将无法远程链接
log-bin=mysql-bin
#在默认情况下mysql会阻止主从同步的数据库function的创建,这会导致我们在导入sql文件时如果有创建function或者使用function的语句将会报错。
log-bin-trust-function-creators=1
#步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_increment=2
#设定数据库中自动增长的起点，两台mysql的起点必须不同，这样才能避免两台服务器同步时出现主键冲突
auto_increment_offset=1
#慢日志
slow-query-log=ON
slow-query-log-file=/opt/safeweb/log/mysql-slow.log
long-query-time=1
#最大链接数
max_connections=1000
#yum安装数据库的设置
sql-mode=‘’

#启动mysql
systemctl start mysqld
systemctl enable mysqld
#查看密码
grep ‘temporary password’ /opt/safeweb/log/mysqld.log

#修改密码方法1
skip_grant_tables
update mysql.user set authentication_string=password(‘123+Jvmp=456’) where user=‘root’;
flush privileges;
#修改密码方法2
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘123+Jvmp=456’;
flush privileges;

#用新密码登陆
mysql -uroot -p123+Jvmp=456
禁止写操作：
flush tables with read lock;
#备份所有数据：
mysqldump -uroot -p123+Jvmp=456 --all-databases > safeweb.sql
#解锁：
unlock tables;
#显示bin-log id
show master status;
#替换
change master to master_host=‘192.168.1.13’,master_port=3306,master_user=‘monitoring_user’,master_password=‘6wfPNxdxjdfhyzfttnvicfaxm0xajoe_’,master_log_file=‘mysql-bin.000002’,master_log_pos=3886;
change master to master_host=‘192.168.1.6’,master_port=3306,master_user=‘monitoring_user’,master_password=‘6wfPNxdxjdfhyzfttnvicfaxm0xajoe_’,master_log_file=‘mysql-bin.000002’,master_log_pos=3886;
#PC1
start slave;
#PC2
start slave;
#PC1和2
show slave status\G
show slave status\G

systemctl enable mysqld