03-26 05:24 阅读 161

监控系统项目实施--安装与部署-- MySQL数据库

在后续的安装中出现了很多问题，多为账号，还有字符集导致的。
安装zabbix，需要使用之前的密码设置格式，还有最重要的字符集是utf8。

主机配置

[root@zabbix-mysql01 ~]# cat /etc/hosts10.10.10.201 zabbix-mysql0110.10.10.202 zabbix-mysql02

安装mysql

[root@zabbix-mysql01 ~]#rpm -ivh http://repo.mysql.com/yum/mysql-8.0-community/el/7/x86_64/mysql80-community-release-el7-3.noarch.rpm [root@zabbix-mysql01 ~]# yum -y install mysql-community-server

配置Mysql

[root@zabbix-mysql01 ~]# cat /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlsymbolic-links=0character-set-server=utf8innodb_file_per_table=1innodb_data_file_path=ibdata1:10M:autoextendserver_id=1[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[root@zabbix-mysql02 ~]# cat /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlsymbolic-links=0character-set-server=utf8innodb_file_per_table=1innodb_data_file_path=ibdata1:10M:autoextendserver_id=2[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

启动数据库

先启动主节点，备节点暂时不启动。

[root@zabbix-mysql01 ~]# systemctl start mysqld[root@zabbix-mysql01 ~]#

修改密码

首先查找临时密码
tail -100 /var/log/messages

Mar 19 22:30:11 zabbix-mysql01 mysqld_pre_systemd: 2021-03-19T14:30:11.846201Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.23) initializing of server in progress as process 3341Mar 19 22:30:11 zabbix-mysql01 mysqld_pre_systemd: 2021-03-19T14:30:11.847519Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Mar 19 22:30:11 zabbix-mysql01 mysqld_pre_systemd: 2021-03-19T14:30:11.851720Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
Mar 19 22:30:12 zabbix-mysql01 mysqld_pre_systemd: 2021-03-19T14:30:12.117691Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
Mar 19 22:30:12 zabbix-mysql01 mysqld_pre_systemd: 2021-03-19T14:30:12.680672Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Viop6I6:eWS!

[root@zabbix-mysql01 ~]# mysql -uroot -pEnter password:
mysql> alter user 'root'@'localhost' identified by 'Root@123456';
mysql> FLUSH PRIVILEGES;

注意zabbix的数据库应该用 utf8,而不应用utf8mb4;
设置密码应该采用#mysql> create database zabbix character set utf8mb4;mysql> create database zabbix character set utf8;
mysql> select host,user from mysql.user;
mysql> CREATE USER 'zabbix'@'%' IDENTIFIED BY 'Root@123456';
mysql> grant all privileges on *.* to 'zabbix'@'%' ;
mysql> FLUSH PRIVILEGES;
mysql> select host,user from mysql.user;

mysql> CREATE USER 'zabbix'@'localhost' IDENTIFIED BY 'Root@123456';
mysql> CREATE USER 'zabbix'@'127.0.0.1' IDENTIFIED BY 'Root@123456';
mysql> grant all privileges on *.* to 'zabbix'@'localhost' ;
mysql> grant all privileges on *.* to 'zabbix'@'127.0.0.1' ;
mysql> FLUSH PRIVILEGES;
设置密码应该采用mysql_native_password方式
alter user 'zabbix'@'%' identified with mysql_native_password by 'Root@123456';
alter user 'zabbix'@'127.0.0.1' identified with mysql_native_password by 'Root@123456';
alter user 'zabbix'@'localhost' identified with mysql_native_password by 'Root@123456';

导入数据库文件

导入数据库文件,该文件需要安装zabbix插件，可参考之前安装的步骤进行操作

wget http://repo.zabbix.com/zabbix/4.0/rhel/7/x86_64/zabbix-release-4.0-2.el7.noarch.rpm
rpm -ivh zabbix-release-4.0-2.el7.noarch.rpm

安装

yum -y install gcc make openldap-devel libssh2-devel net-snmp-devel curl-devel unixODBC-devel OpenIPMI-devel java-devel net-snmp-devel openldap-devel gnutls-devel sqlite-devel sqlite-devel curl-devel libxml2-devel libevent-devel openssl-devel

yum install -y httpd php php-mysql
yum -y install zabbix-server-mysql
yum -y install zabbix-agent安装这些以后，就能有create.sql.gz包
cd /usr/share/doc/zabbix-server-mysql-4.0.29gunzip create.sql.gz

mysql -uzabbix -pRoot@123456 -h127.0.0.1use zabbixsource create.sql

Mysql数据库主库配置

以下操作在主服务器上进行
停止Mysql主库服务
systemctl stop mysqld

同步主库文件到从库

[root@zabbix-mysql01 ~]# rsync -av -e "ssh -p 22" /var/lib/mysql/ root@10.10.10.202:/var/lib/mysql/

启动主库服务

[root@zabbix-mysql01 ~]# systemctl start mysqld

主库上配置复制数据库用户

[root@zabbix-mysql01 ~]# mysql -uroot -pRoot@123456mysql> create user 'repl'@'%' identified with mysql_native_password by 'Root@123456';Query OK, 0 rows affected (0.00 sec)mysql> grant replication client,replication slave on *.* to 'repl'@'%';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql>

查看主库运行状态

mysql> show master status\G*************************** 1. row *************************** File: binlog.000004 Position: 846 Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

MYSQL 数据库从库的配置

启动从节点

需要从节点的/var/lib/mysql/auto.cnf

[root@zabbix-mysql02 mysql]# systemctl start mysqld

连接主库

change master to master_host='10.10.10.201',
MASTER_USER='repl',
master_password='Root@123456',
master_port=3306,
master_log_file='binlog.000004',
master_log_pos=846,
master_connect_retry=10;

查看状态

mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 10.10.10.201                  Master_User: repl                  Master_Port: 3306                Connect_Retry: 10              Master_Log_File: binlog.000004          Read_Master_Log_Pos: 846               Relay_Log_File: zabbix-mysql02-relay-bin.000003                Relay_Log_Pos: 321        Relay_Master_Log_File: binlog.000004             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: 846              Relay_Log_Space: 539              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: 0Master_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: 9d590336-88bf-11eb-905e-000c29a5e8a0             Master_Info_File: mysql.slave_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:        Master_public_key_path:         Get_master_public_key: 0            Network_Namespace: 1 row in set, 1 warning (0.00 sec)

如果不删除从节点的auto.cnf文件，则会报错

mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State:                   Master_Host: 10.10.10.201                  Master_User: repl                  Master_Port: 3306                Connect_Retry: 10              Master_Log_File: binlog.000004          Read_Master_Log_Pos: 846               Relay_Log_File: zabbix-mysql02-relay-bin.000001                Relay_Log_Pos: 4        Relay_Master_Log_File: binlog.000004             Slave_IO_Running: No            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: 846              Relay_Log_Space: 156              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: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 13117                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.               Last_SQL_Errno: 0               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1                  Master_UUID:              Master_Info_File: mysql.slave_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: 210320 00:34:09     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.01 sec)