1、安装mysql5.7
1.1 下载mysql源安装包
wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
1.2 安装mysql源
yum install mysql57-community-release-el7-8.noarch.rpm
1.3 检查mysql源是否安装成功
yum repolist enabled | grep "mysql.*-community.*"
1.4 安装
yum install mysql-community-server
1.5 启动mysql服务
systemctl start mysqld #启动
systemctl status mysqld #状态
1.6 开机启动
systemctl enable mysqld
systemctl daemon-reload
1.7 修改root本地登录密码
grep 'temporary password' /var/log/mysqld.log
ALTER USER 'root'@'localhost' IDENTIFIED BY 'TabY_opaw5';
1.8 表不区分大小写
修改my.cnf,添加一行
>/etc/my.cnf
# 表名不区分大小写
lower_case_table_names=1
重启mysql
>systemctl restart mysqld
2、主从配置
2.1 主库创建同步账号
我们要在主数据库里创建一个账号,并且该账号要授予 REPLICATION SLAVE 权限。
create user 'repl'@'%' identified by 'TabX_opBo5';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
2.2 配置主数据库
要主数据库,你必须要启用二进制日志(binary logging),并且创建一个唯一的Server ID,这步骤可能要重启MySQL。
主服务器发送变更记录到从服务器依赖的是二进制日志,如果没启用二进制日志,复制操作不能实现(主库复制到从库)。
复制组中的每台服务器都要配置唯一的Server ID,取值范围是1到(232)−1,你自己决定取值。
配置二进制日志和Server ID,你需要关闭MySQL和编辑my.cnf或者my.ini文件,在 [mysqld] 节点下添加配置。 编辑my.cnf:
vi /etc/my.cnf
server-id = 1
log_bin = master-bin
log_bin_index = master-bin.index
binlog_do_db = test
binlog_ignore_db = mysql
备注:server-id 服务器唯一标识,log_bin 启动MySQL二进制日志,binlog_do_db 指定记录二进制日志的数据库,binlog_ignore_db 指定不记录二进制日志的数据库。
重启mysql:
systemctl restart mysqld
首先登陆数据库,然后刷新所有的表,同时给数据库加上一把锁,阻止对数据库进行任何的写操作
mysql > flush tables with read lock;
然后执行下面的语句获取二进制日志的信息:
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 | 154 | test | mysql | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set
注意:master-bin.000003和154这两个值要记录下来
这时可以对数据库解锁,恢复对主数据库的操作
mysql > unlock tables;
2.3 配置从数据库
编辑my.cnf:
vi /etc/my.cnf
添加
server-id = 2
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
重启mysql
systemctl restart mysqld
在slave服务器中登陆mysql,连接master主服务器数据库
change master to master_host='主库ip', master_port=3306, master_user='repl', master_password='TabX_opBo5', master_log_file='master-bin.000003', master_log_pos=154;
启动slave:
start slave;
mysql> show slave status
;
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Waiting for master to send event | 172.31.150.25 | root | 3306 | 60 | master-bin.000003 | 154 | slave-relay-bin.000010 | 321 | master-bin.000003 | Yes | Yes | | | | | | | 0 | | 0 | 154 | 528 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 | fa758afe-7a75-11e8-988c-00163e058649 | /var/lib/mysql/master.info | 0 | NULL | Slave has read all relay log; waiting for more updates | 86400 | | | | | | | | 0 | | | |
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
1 row in set
3、测试
在主库的test库中新建表和添加数据库,会自动同步到slave库中。