linux环境安装——mysql集群安装复习——主从复制
1、首先安装mysql：
mkdir -p /soft/mysql8 mkdir -p /evir/mysql8 执行上传文件操作 rpm -qa |grep mariadb 查询是否有这个文件 rpm -e --nodeps mariadb-libs 进行删除 rpm -qa |grep mariadb 查询这个文件是否删干净 tar -xf /soft/mysql8/mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz -C /evir/mysql8/ 解压 cd /evir/mysql8/ mv mysql-8.0.30-linux-glibc2.12-x86_64/ mysql8030 cd mysql8030 mkdir data chmod -R 777 data [root@linux222 mysql8030]# groupadd mysql [root@linux222 mysql8030]# useradd -g mysql mysql [root@linux222 bin]# yum -y install numactl.x86_64 缺少下划线了 [root@linux222 bin]# yum install libaio-devel.x86_64 缺少下划线了 [root@linux222 bin]# rpm -qa|grep libaio [root@linux222 bin]# chmod -R 777 /evir/mysql8/mysql8030/data/ [root@linux222 data]# vim /etc/my.cnf [mysqld] port=3306 basedir=/evir/mysql8/mysql8030/ # MySQL根目录 datadir=/evir/mysql8/mysql8030/data/ # MySQL的data目录 socket=/tmp/mysql.sock character-set-server=UTF8MB4 symbolic-links=0 lower-case-table-names=1 #解决“java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list,references column” sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' [root@linux222 mysql8030]#cp -a ./support-files/mysql.server /etc/init.d/mysql [root@linux222 mysql8030]#chmod +x /etc/init.d/mysql [root@linux222 mysql8030]#chkconfig --add mysql [root@linux222 bin]# ./mysqld --user=mysql --basedir=/evir/mysql8/mysql8030/ --datadir=/evir/mysql8/mysql8030/data/ --lower-case-table-names=1 --initialize [root@linux222 mysql8030]# service mysql start [root@linux222 mysql8030]# service mysql status [root@linux222 mysql8030]# ln -s /evir/mysql8/mysql8030/bin/mysql /usr/bin [root@linux222 mysql8030]# mysql -uroot -p root@localhost: V)kR&i*SK3iy ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456sww#'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
主服务器修改后如下：
server-id = 1
read-only = 0 -可以读 可以写
log-bin=mysql-bin -开启二进制
[root@linux222 ~]# cat /etc/my.cnf [mysqld] port=3306 basedir=/evir/mysql8/mysql8030/ # MySQL根目录 datadir=/evir/mysql8/mysql8030/data/ # MySQL的data目录 socket=/tmp/mysql.sock character-set-server=UTF8MB4 symbolic-links=0 lower-case-table-names=1 server-id = 1 read-only = 0 log-bin=mysql-bin # binlog记录内容的方式，记录被操作的每一行 binlog_format = ROW # 减少记录日志的内容，只记录受影响的列 binlog_row_image = minimal #解决“java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list,references column” sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' [root@linux222 ~]#
主机上操作：
登录mysql，创建远程连接的账号，并授予主从复制权限。
在主库上面运行，账号是从库连接主库的。
%：在任何主机上登录密码，都可以连接mysql。
#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务 CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456'; #为 'itcast'@'%' 用户分配主从复制权限 GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';
通过指令，查看二进制日志坐标。
show master status;
从上面配置：
修改配置文件 /etc/my.cnf。
#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2的32次方减1，和主库不一样即可 server-id=2 #是否只读,1 代表只读, 0 代表读写 （普通用户） read-only=1 #设置超级管理员只读（可设可不设） super-read-only=1
mysql> stop replica; Query OK, 0 rows affected (0.01 sec) mysql> reset master; Query OK, 0 rows affected (0.01 sec) mysql> reset replica; Query OK, 0 rows affected (0.02 sec) mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='47.120.61.196', -> SOURCE_USER='itcast', -> SOURCE_PASSWORD='123456www', -> SOURCE_LOG_FILE='mysql-bin.000003', -> SOURCE_LOG_POS=783;
mysql> start replica ; Query OK, 0 rows affected (0.02 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 47.120.61.196 Master_User: itcast Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 783 Relay_Log_File: hcss-ecs-d318-relay-bin.000002 Relay_Log_Pos: 326 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes 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: 783 Relay_Log_Space: 544 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: ece09736-f89c-11ee-8014-00163e04751f Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 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: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec)
==========================================================
注意，如果从库连接不上主库：
2、 先让所有的MYSQL数据库的UUID保持不同(如果你是直接复制的安装好MYSQL的虚拟机，那么每个虚拟机上搭载的MYSQL数据库UUID是一致的)
vi /data/mysql_data/auto.cnf
[auto]
server-uuid=f787ff18-b491-11eb-9910-080027c8eeff
如果大家发现所有的MYSQL的uuid都是一个，那么你就给他改一下：
[auto]
server-uuid=f787ff18-b491-11eb-9910-080027c8eefa
===========================================================
