以mysql8.0.20为示例
1.1、环境规划
服务器IP | 主从角色 |
---|---|
192.168.0.172 | 主库 |
192.168.0.176 | 从库 |
1.2、mariadb的卸载
查看是否安装了mariadb数据库
命令:rpm -qa | grep mariadb
根据上一步骤查询出来的名称进行卸载(mariadb数据库组件全部卸载)
命令:rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
1.3、安装 MySQL (主从数据库都需操作)
1.3.1上传并解压 MySQL 安装包到自定义规划目录
1)进入自定义规划目录
命令:cd /opt/src
2)上传上述下载的安装到到该目录
命令:rz
3)解压压缩包到该目录
命令:tar -xvf mysql-8.0.20-1.el7.x86_64.rpm-bundle.tar
1.3.2 按照依赖关系依次安装 rpm 包
1)依赖关系依次为 common → libs → client → server → libs-compat
rpm -ivh mysql-community-common-8.0.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.20-1.el7.x86_64.rpm
2)卸载rpm方式安装的MySQL(需要做卸载时执行)
a)列出所有 rpm 方式安装的 MySQL
命令:rpm -qa | grep mysql
b)通过如下命令卸载 rpm 安装的 MySQL
命令:rpm -e
列出的名称(按照安装顺序的反方向删除)
3)通过 rpm 卸载后,如需再次安装,请删除配置文件 (需要做卸载时执行)
命令:rm -rf /var/lib/mysql/*
命令:rm -rf /var/log/mysql*
1.4、初始化root密码
1.4.1 将3306加入防火墙白名单
firewall-cmd --zone=public --list-ports
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
1.4.2 修改数据库配置文件参数
vim /etc/my.cnf
在文件后最后添加
lower_case_table_names=1
1.4.3 启动数据库
systemctl start mysqld
1.4.4 查看MySQL默认初始化密码
cat /var/log/mysqld.log | grep password
1.4.5 登录到mysql数据库,修改root密码
[root@aimmysql0001 src]# mysql -u root -p
Enter password:
---------------------------------
mysql> set password='密码';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
1.4.6 设置远程登录
重新登录
[root@aimmysql0001 src]#mysql -u root -p
Enter password:
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set host = "%" where user='root';
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| % | root | *B17EFECCEB1C303A94ACD239D5A37282F863B735 | mysql_native_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)
另一个方法,没试验过,但是意思看着是一样的,因为本身自带root账号,其实不需要分配权限,可以参照这个设置其他账号相关权限。
命令:CREATE USER 'root'@'%' IDENTIFIED BY '密码';
命令:GRANT ALL ON *.* TO 'root'@'%';
命令:ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
命令:FLUSH PRIVILEGES;
1.5、修改mysql工作目录及主从配置文件
1.5.1 修改主库配置文件
停止mysql服务:service mysqld stop
vim /etc/my.cnf
新增或参照修改以下参数:
log-bin=mysql-bin
server-id=1
#主节点id用1
innodb_buffer_pool_size = 40960M
max_connections=5000
innodb_file_per_table=1
innodb_log_file_size=256M
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_io_capacity=5000
innodb_io_capacity_max=50000
innodb_thread_concurrency=0
innodb_buffer_pool_instances=4
skip-name-resolve
lower_case_table_names = 1
#此处修改工作目录
datadir=/opt/mysql
socket=/opt/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
1.5.2 调整主库mysql工作目录
#拷贝mysql文件至/opt目录
cp -rf /var/lib/mysql /opt
#赋予mysql:mysql用户和用户组
chown -R mysql:mysql /opt/mysql
#启动Mysql
service mysqld start
1.5.3 修改从库配置文件
停止mysql服务:service mysqld stop
vim /etc/my.cnf
新增或参照修改以下参数:
server-id=2
innodb_buffer_pool_size = 40960M
max_connections=5000
innodb_file_per_table=1
innodb_log_file_size=256M
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_io_capacity=5000
innodb_io_capacity_max=50000
innodb_thread_concurrency=0
innodb_buffer_pool_instances=4
skip-name-resolve
lower_case_table_names=1
datadir=/opt/mysql
socket=/opt/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
1.5.4 调整从库mysql工作目录
#拷贝mysql文件至/opt目录
cp -rf /var/lib/mysql /opt
#赋予mysql:mysql用户和用户组
chown -R mysql:mysql /opt/mysql
#启动Mysql
service mysqld start
1.6、主从设置
1.6.1 主库设置
1、登录数据库
mysql -h 127.0.0.1 -uroot -p
Enter password:
2、新建主从同步账号
mysql> CREATE USER 'repl'@'从库ip' IDENTIFIED WITH mysql_native_password BY '同步账号的密码';
Query OK, 0 rows affected (0.00 sec)
3、给主从同步账号赋予REPLICATION权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从库ip';
Query OK, 0 rows affected (0.00 sec)
4、更新数据
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
5、查主库状态信息
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 838 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
1.6.2 从库设置
1、登录数据库
mysql -h 127.0.0.1 -uroot -p
Enter password:
2、建立主从关系
mysql> CHANGE MASTER TO MASTER_HOST='主库ip',MASTER_USER ='repl', MASTER_PASSWORD='同步账号的密码',MASTER_LOG_FILE='主库的File值',MASTER_LOG_POS=主库Position值;
Query OK, 0 rows affected, 1 warning (0.02 sec)
3、启动从库
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
4、查看主从状态
mysql> show slave status\G
1.7、验证
在主库创建数据库,如数据库名:test,字符集:utf8mb4,查看从库是否同步
取消主从同步
在从库服务器
mysql> stop slave;
mysql> reset slave;
1、reset slave
将使SLAVE忘记主从复制关系的位置信息。该语句将被用于干净的启动,
删除master.info和relay-log.info文件;
删除所有的relay log(包括还没有应用完的日志),创建一个新的relay log文件;
但是内存中主从连接信息依然存在
2、reset slave all;
Mysql 5.5开始,多了一个all参数。如果不加all参数,那么所有的连接信息仍然保留在内存中,包括主库地址、端口、用户、密码等。这样可以直
接运行start slave命令而不必重新输入change master to命令,而运行show slave status也仍和没有运行reset slave一样,有正常的输出。
但如果加了all参数,那么这些内存中的数据也会被清除掉,运行show slave status就输出为空了。
3、reset master;
删除所有的binglog日志文件,并将日志索引文件清空,重新开始所有新的日志文件,一般不执行。
处理sock找不到问题
/etc/my.cnf指定了/opt/mysql/mysql.sock文件,所以做个软链接即可
ln -s /opt/myslq/mysql.sock /var/lib/mysql/