环境两台服务器[一主一从]
服务器IP Centos版本 服务
192.168.10.152 CentOS Linux release 7.6.1810 (Core) MYSQL5.7（主）
192.168.10.153 CentOS Linux release 7.6.1810 (Core) MYSQL5.7（从）
root用户操作
一、MySQL安装
1、关闭防火墙
sudo systemctl stop firewalld && setenforce 0
2、检查有没有MySQL的服务
rpm -qa | grep -i mysql
卸载：rpm -e --nodeps mysql-libs-5.1.73-7.el6.x86_64
rpm -qa | grep mariadb
卸载mariadb（centos7默认安装)：rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
3、 将mysql的包上传到/usr/local/src，并解压到/usr/local目录下
cd /usr/local/src
tar -xzvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
cd /usr/local/
mv mysql-5.7.18-linux-glibc2.5-x86_64 mysql-5.7.18
ln -s /usr/local/mysql-5.7.18 /usr/local/mysql
4、创建mysql用户
groupadd mysql ----创建mysql用户组组
useradd -r -g mysql mysql ----创建mysql用户并添加到mysql用户组中
chown -R mysql:mysql /usr/local/mysql/ ----将mysql目录访问权限赋为myql用户
5、新建数据目录和日志目录
日志目录：
cd /var/log/
touch mysqld.log
chmod 777 mysqld.log
chown mysql:mysql mysqld.log
数据目录：
mkdir -p /usr/local/mysql/data
chown -R mysql:mysql /usr/local/mysql/data
pid目录
cd /var/run/
mkdir mysqld
chmod 777 mysqld
cd mysqld
touch mysqld.pid
chmod 777 mysqld.pid && chown mysql:mysql mysqld.pid
6、创建配置文件
vim /etc/my.cnf
复制以下内容
[client]
port = 3306
default-character-set = utf8
socket = /tmp/mysql.sock
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket = /tmp/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_bin_trust_function_creators=1
lower_case_table_names=1
sql_mode=
transaction_isolation=READ-COMMITTED
binlog_format=mixed
max_allowed_packet=104857600
character-set-server = utf8
collation-server = utf8_general_ci
max_connections=1000
max_user_connections=500
interactive_timeout = 28800
wait_timeout = 28800
net_read_timeout = 28800
net_write_timeout = 28800
connect_timeout = 28800
7、初始化数据库 --------一整条命令
初始化
cd /usr/local/mysql/bin/
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --lc_messages_dir=/usr/local/mysql/share --lc_messages=en_US
查看日志查找root密码
cat /var/log/mysqld.log|grep root@localhost
8、启动mysql并登陆数据库修改root密码
启动mysql
/usr/local/mysql/support-files/mysql.server start 或者 service mysqld start/usr/local/mysql/bin/mysqld_safe &
登陆mysql
/usr/local/mysql/bin/mysql -uroot -p
如果一直提示错误，修改配置文件/etc/my.cnf，登录数据库时跳过输入密码
skip-grant-tables
重启mysql
/usr/local/mysql/support-files/mysql.server restart 或者 service mysqld restart
/usr/local/mysql/bin/mysql -uroot -p --------不需要密码即可登录
修改新的root密码
update mysql.user set authentication_string=password('1qaz@WSX') where user='root' ;
注释掉/etc/my.cnf配置文件中的skip-grant-tables
重启mysql
/usr/local/mysql/support-files/mysql.server restart 或者 service mysqld restart
登录
/usr/local/mysql/bin/mysql -uroot -p1qaz@WSX
9、设置开机自动启动
cd /usr/local/mysql/support-files
cp mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
10、建立软连接
ln -s /usr/local/mysql/bin/mysqld /usr/bin
二、主从数据库配置
1、修改配置文件
1、配置Master主库机器---192.168.10.152
vim /etc/my.cnf
server-id=1 ##当前数据库在集群中的id值
log-bin=mysql-bin ##启用二进制日志;key值的名字不能变,value表示你定义 的二进制文件的名字.可以自定义
登录mysql
/usr/local/mysql/bin/mysql -uroot -p1qaz@WSX
mysql>flush tables with read lock; #数据库锁表，不让写数据;这步骤可不做 对于当前环境的mysql无需使用lock命令,因为没有人操作,但是生产环境中必须这样做
ALTER USER 'root'@'localhost' IDENTIFIED BY '1qaz@WSX';
mysql>GRANT REPLICATION SLAVE ON *.* to 'root'@'%' identified by '1qaz@WSX';
mysql>show master status; #查看MASTER状态(这两个值File和Position)其中的file就是二进制文件,position记录当前操作sql的步骤数(注意一条sql包含多步,所以不是sql语句的条数)
2、配置从库服务器---192.168.10.153
修改/etc/my.cnf增加一行
vim /ect/my.cnf
server-id=2
重启服务
/usr/local/mysql/support-files/mysql.server restart 或者 service mysqld restart
登录mysql
/usr/local/mysql/bin/mysql -uroot -p1qaz@WSX
通过mysql命令配置同步日志的指向: 登录到从节点的mysql客户端(linux,sqlyog)
mysql>change master to
master_host='192.168.10.152',
master_port=3306,
master_user='root',
master_password='1qaz@WSX',
master_log_file='mysql-bin.000001', ###和主服务器show master status中的File字段值相同
master_log_pos=398; ##和主服务器show master status中的Position字段值相同
mysql>start slave; #开启从节点服务状态;stop slave;停止服务，出错时先停止停止参考下方操作，再重新配置
mysql>show slave status\G #查看SLAVE状态，\G结果纵向显示。必须大写,这个命令无法再sqlyog中使用,linux客户端使用的;
测试不通过:
mysql>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.10.152
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 398
Relay_Log_File: crop003-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 398
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1130
Last_IO_Error: error connecting to master 'root@192.168.10.152:3306' - retry-time: 60 retries: 5
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 210520 11:17:34
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> quit
停掉从库
1.
mysql> stop slave io_thread;
2.
mysql> show status like 'Slave_open_temp_tables';
3.
如果'Slave_open_temp_tables'不为0，就执行如下，否则跳到第四步
mysql> start slave io_thread
4.
#service mysqld stop
即先停主从的IO进程，确认临时表上的更新是否都写入磁盘，临时表是否为0，若不为0，等IO完了，再关从库
存在临时表的数据更新步骤，主从同步中调用IO_thread，将主库的binlog日志同步到从库
报错问题
解决办法:
1.先关闭当前的slave;
mysql>stop slave;
2.检查主服务器的配置信息:
vi /etc/my.cnf # 文件末尾查看追加
server-id=1
log-bin=mysql-bin
binlog-format=ROW
binlog-do-db=msb
3.重启mysqld
service mysqld restart
4.查看主的状态信息,在主MySQL下
/usr/local/mysql/bin/mysql -uroot -p1qaz@WSX
mysql>show master status;
记住Position,
进入从库重新关联，从库设置成功，show slave status\G不再报错:
