一、安装mysql,注意不是mariadb(以下为linux-centos7.6环境下)

主从节点都这么装也不麻烦,mysql8.0+才能用clone并且子节点克隆的mysql还需要重新生成uuid文件

[root@VM-0-15-centos etc]# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
[root@VM-0-15-centos etc]# rpm -ivh mysql-community-release-el7-5.noarch.rpm
[root@VM-0-15-centos etc]# yum install mysql-community-server

二、启动mysql服务,进行基本设置

[root@VM-0-15-centos etc]# service mysqld restart

初次安装mysql,root账户没有密码,登陆mysql设置密码。

1、初次登陆
[root@VM-0-15-centos etc]# mysql -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

2、设置密码重新登陆
mysql> set password for 'root'@'localhost' = password('111'); # 设置root用户密码
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@VM-0-15-centos etc]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> 
3、设置字符集utf8mb4

采用utf8mb4编码的原因是:存储与获取数据的时候,不用再考虑表情字符的编码与解码问题。
utf8mb4的最低mysql版本支持版本为5.5.3+最低mysql驱动版本5.1.13+
修改mysql配置文件my.cnf(windows为my.ini)
my.cnf一般在etc/my.cnf位置。找到后请在以下三部分里添加如下内容:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

重启数据库检查设置是否生效

[root@VM-0-15-centos etc]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@VM-0-15-centos etc]# mysql -uroot -p111
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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 VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%'; # 查看数据库字符集
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8mb4_unicode_ci         |
| collation_database       | utf8mb4_unicode_ci         |
| collation_server         | utf8mb4_unicode_ci         |
+--------------------------+----------------------------+
11 rows in set (0.00 sec)

mysql>

如上显示则配置成功。

4、远程连接设置
mysql> grant all privileges on *.* to root@'%'identified by '111';
Query OK, 0 rows affected (0.00 sec)

PS: 正式标准的服务配置,应该有专门的数据库同步用户仅赋予同步权限,抱着先跑起来的想法,此处小小偷懒一波

三、集群主从节点配置

1、/etc/my.cnf 添加如下配置(必须在(# Recommended in standard MySQL setup)注释下,不然不会生效)
# Recommended in standard MySQL setup
server_id=64 # 配置服务唯一id,建议为ip地址最后一部分,主从不可相同
log-bin=mysql-bin # 开启binlog日志,从节点可不开启
# 指定不同步的数据库(原理是不生成指定库的binlog日志,主节点设置即可,可不配置)
binlog-ignore-db=performance_schema 
binlog-ignore-db=information_schema
binlog-ignore-db=sys
2、查看主节点binlog日志状态信息,待会儿配置从节点要用到
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3、从节点同步配置
# 每一项都可单独修改,但需要停止同步再操作(stop slave)
CHANGE MASTER TO # 修改配置命令
MASTER_HOST='xxx.x.x.x', # 主节点ip地址 
MASTER_PORT=3306, # 主节点mysql实例端口
MASTER_USER='root', # 主节点mysql同步用的账号
MASTER_PASSWORD='111', # 主节点mysql同步用的账号密码
MASTER_LOG_FILE='mysql-bin.000002', # 主节点开始同步的文件(上一步操作获得的信息‘File’)
MASTER_LOG_POS=120;  # 主节点开始同步的文件日志位置(上一步操作获得的信息‘Position’)

配置完成后启动同步

mysql> CHANGE MASTER TO
    -> MASTER_HOST='xxx.x.x.x',
    -> MASTER_PORT=3306,
    -> MASTER_USER='root',
    -> MASTER_PASSWORD='111',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave; # 开启主从同步
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G; # 查看主从同步状态信息
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxx.x.x.x
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 453
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 616
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes # 取主节点日志的线程, Yes 为正在运行
            Slave_SQL_Running: Yes # 从日志恢复数据的线程, 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: 453
              Relay_Log_Space: 790
              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: 64
                  Master_UUID: 9b5df4cc-3e2b-11ec-8842-5254004d6c1c
             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 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)

上面标注的三个参数为关键参数如上显示则同步状态正常,随笔到此结束,接下来就是手动体验数据库主从同步的功能啦。

Logo

瓜分20万奖金 获得内推名额 丰厚实物奖励 易参与易上手

更多推荐