1 mysql 5.6.22版本的安装
1.1 下载安装包mysql-5.6.34-linux-glibc2.5-x86_64.tar
1.2 卸载自带的Mariadb
[root@localhost ~]# rpm -qa|grep mariadb // 查询出来已安装的mariadb
[root@localhost ~]# rpm -e --nodeps 文件名 // 卸载mariadb,文件名为上述命令查询出来的文件
1.3 删除etc目录下的my.cnf文件(如果有的话)
[root@localhost ~]# rm /etc/my.cnf
1.4 执行以下命令来创建mysql用户组
[root@localhost ~]#groupadd mysql
1.5 执行以下命令来创建一个用户名为mysql的用户并加入mysql的用户组
[root@localhost ~]#useradd -g mysql mysql
1.6 将下载的压缩包放到/usr/local/目录下(可通过xftp软件进行移动)
1.7 加压安装包
将压缩包mysql-5.6.34-linux-glibc2.5-x86_64.tar.gz解压
解压缩命令为:#tar -zxvf tar -zxvf mysql-5.6.34-linux-glibc2.5-x86_64.tar.gz
1.8 将解压好的文件夹重命名为mysql
[root@localhost local]# mv 解压出来的文件夹名 mysql
1.9 直接copy一份my.cnf文件到/etc下,然后配置
#cd /usr/local/mysql/support-files
[root@localhost support-files]#cp my-default.cnf /etc/my.cnf
#配置
[root@localhost support-files]#vim /etc/my.cnf
通过vim编辑器编辑my.cnf代码如下:
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock
[mysqld]
skip-name-resolve
#设置3306端口
port = 3306
socket=/var/lib/mysql/mysql.sock
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_name=1
max_allowed_packet=16M
1.10 进入安装mysql软件目录
[root@localhost ~]#cd /usr/local/mysql
[root@localhost mysql]# chown -R mysql:mysql ./ 修改当前目录拥有着为mysql用户
[root@localhost mysql]#yum -y install autoconf 安装autoconf库
# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/ 安装数据库
[root@localhost mysql]#chown -R mysql:mysql data 修改当前data目录的拥有者为mysql用户
到此数据库安装完毕!
2 配置MySQL
2.1授权
[root@localhost ~]#chmod 644 /etc/my.cnf #my.cnf设置为用户可读写,其他用户不可写.
2.2 复制启动脚本到资源目录
[root@localhost mysql]# cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld
2.3 增加mysqld服务控制脚本执行权限
[root@localhost mysql]# chmod +x /etc/rc.d/init.d/mysqld
2.4 将mysqld服务加入到系统服务
[root@localhost mysql]# chkconfig --add mysqld
2.5 检查mysqld服务是否已经生效
[root@localhost mysql]# chkconfig --list mysqld
2.6 启动mysqld
[root@localhost mysql]# service mysqld start
2.7 将mysql的bin目录加入PATH环境变量,编辑 ~/.bash_profile文件
[root@localhost mysql]# vim ~/.bash_profile
在文件最后添加如下信息:
export PATH=$PATH:/usr/local/mysql/bin
然后按ESC键
继续 shift键加冒号打出来=> :
接下来输入wq回车即可
执行下面的命令是修改的内容立即生效:
[root@localhost mysql]# source ~/.bash_profile
2.8 以root账户登录mysql,默认是没有密码的
[root@localhost mysql]# mysql -uroot -p
要输入密码的时候直接回车即可。
2.9 设置root账户密码为ywaqsj@1.0@(也可以修改成你要的密码)
mysql>use mysql;
mysql>update user set password=password('root') where user='root' and host='localhost';
mysql>flush privileges;
2.10 设置远程主机登录,注意下面的your username 和 your password改成你需要设置的用户和密码
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'ywaqsj@1.0@' WITH
GRANT OPTION;
2.11 用navicat来连接数据库,显示11060错误,可能是3306端口没打开
[root@localhost mysql]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@localhost mysql]# firewall-cmd --reload
success
2.12 数据库安装完成。
3 主从复制环境搭建
192.168.103.54(主)
192.168.103.55(从)
192.168.103.56(从)
3.1.配置主从复制同步(先配置主服务器)192.168.103.54
#在[mysqld]中添加:
server-id=54
log-bin=master-a-bin
binlog-format=ROW
binlog_ignore_db=mysql,information_schema
3.2 配置从服务器登录主服务器的账号授权
目的:是让从服务器能够发现主服务器,步骤是在主服务器中实现的
#进入mysql数据库
[root@cluster1 home]# mysql -u root -p
Enter password:
#创建从数据库的masterbackup用户和权限
mysql>grant replication slave on *.* to 'root'@'192.168.42.129' identified by 'root';
#备注
#192.168.42.%通配符,表示0-255的IP都可以访问主服务器,正式环境请配置制定从服务器IP
#若将192.168.42.%改为%,则任何IP均可作为其从数据库来访问主服务器
#刷新
mysql> flush PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#退出,配置从服务器登录主服务器的账号授权完成
mysql> exit;
Bye
3.3.配置从服务器(vi /etc/my.cnf)192.168.103.55
#在[mysqld]中添加:
#日志文件名称
log_bin=master-a-bin
#二进制日志的格式,跟主服务器一样
binlog-format=ROW
#各个服务器的id,这个必须是唯一的,和主服务器不一样
server-id=2
#双主互相备份,“从服务器可以作为主机存在,将数据传给其他服务器”
#log-slave-updates=true
3.4 重启主服务器
#重启mysql的服务
[root@cluster1 home]#service mysqld restart
[root@cluster1 home]# mysql -u root -p
#查看主服务器的状态
[root@cluster1 home]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@cluster1 home]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+---------------------+----------+--------------+------------------+----------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |Executed_Gtid_Set|
+---------------------+----------+--------------+------------------+----------------+
| master-a-bin.000001 | 120 | test | | |
+---------------------+----------+--------------+------------------+----------------+
1 row in set (0.00 sec)
mysql>
3.4 从服务器配置
#重启mysql服务器
service mysqld restart
[root@cluster1 home]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
#登录mysql
[root@cluster1 home]# mysql -u root -p
Enter password:
#设置主服务器的日志和偏移量
change master to master_host='192.168.103.54',
master_port=3306,
master_user='root',
master_password='ywaqsj@1.0@',
master_log_file='master-a-bin.000001',
master_log_pos=120;
#master_host 主服务器主机
#master_port 主服务器端口
#启动slave数据同步
mysql>start slave;
#停止slave数据同步(若有需要)
mysql>stop slave;
#查看slave信息
show slave status\G;
mysql>show slave status\G;
操作如下所示:
mysql> change master to master_host='192.168.103.54',
-> master_port=3306,
-> master_user='root',
-> master_password='ywaqsj@1.0@',
-> master_log_file='master-a-bin.000001',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.103.54
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-a-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: cluster1-relay-bin.000001
Relay_Log_Pos: 286
Relay_Master_Log_File: master-a-bin.000001
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: 120
Relay_Log_Space: 462
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: 54
Master_UUID: 289dbf32-fcd5-11ea-98de-000c29779c83
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 the slave I/O thread to update it
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
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
3.5 另一台56从服务器是同55一样的