励志做一个合格的保姆。。。。。。。。。
1、准备两台机器解压mysql的tar包
[root@bigdata01 opt]# tar xvf mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar
mysql-community-server-5.7.18-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.18-1.el7.x86_64.rpm
mysql-community-devel-5.7.18-1.el7.x86_64.rpm
mysql-community-client-5.7.18-1.el7.x86_64.rpm
mysql-community-common-5.7.18-1.el7.x86_64.rpm
mysql-community-embedded-5.7.18-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.18-1.el7.x86_64.rpm
mysql-community-libs-5.7.18-1.el7.x86_64.rpm
mysql-community-server-minimal-5.7.18-1.el7.x86_64.rpm(这个包可以删除)
mysql-community-test-5.7.18-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.18-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.18-1.el7.x86_64.rpm
2、解压后删除该文件
root@bigdata-01 opt]# rm mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar -f
3、创建mysql文件夹,并将mysql的rpm包移动至该文件夹。
[root@bigdata01 opt]# mkdir mysql
[root@bigdata01 opt]# rm -f mysql-community-server-minimal-5.7.18-1.el7.x86_64.rpm
[root@bigdata01 opt]# mv mysql-community-* mysql
[root@bigdata01 opt]# cd mysql/
4、检查Linux是否安装createrepo命令,没有则yum安装
[root@bigdata-01 opt]# yum -y install createrepo
5、把mysql的这些rpm包做成一个私有包仓库
[root@bigdata-01 mysql]# createrepo ./
Spawning worker 0 with 12 pkgs
Workers Finished
Saving Primary metadata
Saving file lists metadata
Saving other metadata
Generating sqlite DBs
Sqlite DBs complete
6、配置mysql的yum源文件。
[root@bigdata01 mysql]# cd /etc/yum.repos.d/
[root@bigdata01 yum.repos.d]# vim mysql.repo
[mysql]
name=5.7
baseurl=file:///opt/mysql/
enable=1
gpgcheck=0
7、检查mysql的yum源是否做好
[root@bigdata01 yum.repos.d]# yum repolist
源标识 源名称 状态
mysql 5.7 12
repolist: 12
8、卸载mariadb的lib避免干扰mysql
[root@bigdata01 yum.repos.d]# rpm -qa | grep mariadb
mariadb-libs-5.5.44-2.el7.centos.x86_64
[root@bigdata01 yum.repos.d]# rpm -e mariadb-libs-5.5.44-2.el7.centos.x86_64 –nodeps
9、安装mysql数据库,此处可注释掉本地center os 7的yum源,防止安装maridb。
[root@bigdata01 yum.repos.d]# yum -y install mysql
[root@bigdata01 yum.repos.d]# yum -y install mysql-server
10、启动mysql服务
[root@bigdata01 log]# systemctl start mysqld.service
11、查看mysql的root用户初始密码
[root@bigdata01 log]# grep 'temporary password' /var/log/mysqld.log
2021-02-18T12:03:18.295554Z 1 [Note] A temporary password is generated for root@localhost: Rieh!a-d,9JH
12、检测登陆mysql
[root@bigdata01 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18
Copyright (c) 2000, 2017, 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>
13、5.7版本的mysql第一次登陆必须修改密码
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
14、修改mysql管理账户root的本地登陆密码
mysql的密码要符合密码安全等级策略的,所以在以后的搭建中养成使用复杂密码的习惯。
mysql> alter user 'root'@'localhost' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> alter user 'root'@'localhost' identified by '1qaz@WSX';
Query OK, 0 rows affected (0.00 sec)
15、修改虚拟机时间
因为我用两台虚拟机并没有链接外网,时间服务器也并没有做,所以在这里同步下时间
第一台
查看当前时间
[root@bigdata01 mysql]# date
2021年 02月 27日 星期六 16:54:32 CST
修改当前机器时间
[root@bigdata01 mysql]# date -s "2021-2-27 13:00:00"
2021年 02月 27日 星期六 13:00:00 CST
查看时间是否修改
[root@bigdata01 mysql]# date
2021年 02月 27日 星期六 13:00:07 CST
将时间写入bios避免重启失效
[root@bigdata01 mysql]# hwclock -w
第二台同样操作,可以同时开两个窗口,同事发送修改时间指令。
[root@bigdata02 mysql]# date
2021年 02月 26日 星期五 23:40:52 EST
[root@bigdata02 mysql]# date -s "2021-2-27 13:00:00"
2021年 02月 27日 星期六 13:00:00 EST
[root@bigdata02 mysql]# date
2021年 02月 27日 星期六 13:00:02 EST
[root@bigdata02 mysql]# hwclock -w
16、现在进行主从配置172.16.176.1节点
bigdata01 172.16.176.1 主节点
bigdata02 172.16.176.2 从节点
编辑修改主节点配置文件并重启mysql服务
在/etc/my.cnf配置文件中增加如下
启用二进制日志文件
添加:log-bin = mysql-bin
添加 : server-id =1
添加:innodb-file-per-table =ON
添加:skip_name_resolve=ON
重启mysql服务
[root@bigdata01 etc]# systemctl start mysqld.service
17、进入mysql查看二进制log是否开启,查看log_bin选项是否为on
[root@bigdata01 etc]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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 global variables like '%log%';
+-----------------------------------------+------------------------------------------+
| Variable_name | Value |
+-----------------------------------------+------------------------------------------+
| back_log | 80 |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /var/lib/mysql/bigdata01.log |
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_write_ahead_size | 8192 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /var/log/mysqld.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_basename | /var/lib/mysql/bigdata01-relay-bin |
| relay_log_index | /var/lib/mysql/bigdata01-relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/bigdata01-slow.log |
| sql_log_off | OFF |
| sync_binlog | 1 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+-----------------------------------------+------------------------------------------+
71 rows in set (0.01 sec)
18、查看主节点二进制日志列表
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
19、查看主节点的server_id
mysql> show global variables like '%server%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| character_set_server | latin1 |
| collation_server | latin1_swedish_ci |
| innodb_ft_server_stopword_table | |
| server_id | 1 |
| server_id_bits | 32 |
| server_uuid | 58be9b42-77f5-11eb-bdd7-000c29cadc9d |
+---------------------------------+--------------------------------------+
6 rows in set (0.00 sec)
20、在主节点上创建有复制权限的用户
赋权repluser在172.16.176.2通过密码1qaz#EDC访问
mysql> grant replication slave,replication client on *.* to 'repluser'@'172.16.176.2' identified by '1qaz#EDC';
Query OK, 0 rows affected, 1 warning (0.00 sec)
刷新配置
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
21、配置从节点172.16.176.2从节点
编辑/etc/my.cnf文件在结尾添加如下内容
添加: relay-log=relay-log
添加: relay-log-index=relay-log.index
添加: server-id=2
添加: innodb_file_per_table=ON
添加: skip_name_resolve=ON
22、启动mysql服务并登陆mysql
[root@bigdata02 mysql]# systemctl start mysqld.service
[root@bigdata02 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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>
23、查看中继日志是否开启relay_log是否是relay_log
mysql> show global variables like "%log%";
+-----------------------------------------+-----------------------------------+
| Variable_name | Value |
+-----------------------------------------+-----------------------------------+
| back_log | 80 |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /var/lib/mysql/bigdata02.log |
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_write_ahead_size | 8192 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /var/log/mysqld.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | relay-log |
| relay_log_basename | /var/lib/mysql/relay-log |
| relay_log_index | /var/lib/mysql/relay-log.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/bigdata02-slow.log |
| sql_log_off | OFF |
| sync_binlog | 1 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+-----------------------------------------+-----------------------------------+
71 rows in set (0.01 sec)
24、查看server相关信息
mysql> show global variables like "%server%";
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| character_set_server | latin1 |
| collation_server | latin1_swedish_ci |
| innodb_ft_server_stopword_table | |
| server_id | 2 |
| server_id_bits | 32 |
| server_uuid | fe1d2b42-78b2-11eb-91a9-000c29c6b096 |
+---------------------------------+--------------------------------------+
6 rows in set (0.00 sec)
25、在从节点配置访问主节点的参数信息
添加 主节点主机,访问主节点的用户名及密码,主节点二进制文件信息。
注意:主节点的二进制文件一定要是二进制列表中的最后一个二进制
mysql> CHANGE MASTER TO MASTER_HOST='172.16.176.1',MASTER_USER='repluser',MASTER_PASSWORD='1qaz#EDC', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=624;
26、查看从节点状态并未运行,这是我们还没有开启从节点的复制线程,IO线程 和 SQL 线程都为NO.
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.176.1
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 624
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 624
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: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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:
1 row in set (0.00 sec)
ERROR:
No query specified
27、启动slave线程
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: 172.16.176.1
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 624
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-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: 624
Relay_Log_Space: 521
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: 58be9b42-77f5-11eb-bdd7-000c29cadc9d
Master_Info_File: /var/lib/mysql/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:
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)
ERROR:
No query specified
28、测试功能
主节点172.16.176.1上登录mysql创建数据库mydb
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 783 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
29、去从节点172.16.176.2查看二进制日志并查看数据库是否创建
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.176.1
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 783
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 479
Relay_Master_Log_File: mysql-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: 783
Relay_Log_Space: 680
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: 58be9b42-77f5-11eb-bdd7-000c29cadc9d
Master_Info_File: /var/lib/mysql/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:
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)
ERROR:
No query specified
总结:
安装过程中整体比较顺利,遇到问题如下:
1、这个包一定要删掉,mysql-community-server-minimal-5.7.18-1.el7.x86_64.rpm。
2、防火墙一定要关掉。
3、注意输入法切换时中英文符号。
4、一定要注意操作的机器ip别主节点操作当作从节点。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。