MYSQL数据库管理-mysql数据库简易部署

1 关闭iptables以及selinux等

2 卸载系统自身安装的mysql和mariadb

[root@localhost ~]# rpm -qa|grep mysql
qt-mysql-4.8.7-2.el7.x86_64
akonadi-mysql-1.9.2-4.el7.x86_64
[root@localhost ~]# rpm -qa|grep mariadb
mariadb-5.5.56-2.el7.x86_64
mariadb-libs-5.5.56-2.el7.x86_64
mariadb-server-5.5.56-2.el7.x86_64

[root@localhost ~]# rpm -e mysql
error: package mysql is not installed
[root@localhost ~]# rpm -e --nodeps qt-mysql-4.8.7-2.el7.x86_64
[root@localhost ~]# rpm -e --nodeps akonadi-mysql-1.9.2-4.el7.x86_64
[root@localhost ~]# rpm -e --nodeps mariadb-5.5.56-2.el7.x86_64
[root@localhost ~]# rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
/sbin/ldconfig: /lib64/libcrypto.so.1.0.0 is not a symbolic link

/sbin/ldconfig: /lib64/libssl.so.1.0.0 is not a symbolic link

[root@localhost ~]#  rpm -e --nodeps mariadb-server-5.5.56-2.el7.x86_64
[root@localhost ~]# 

3 上传安装包，创建目录，并解压

[root@localhost ~]# mkdir /usr/local/mysql
[root@localhost patrol]# mv mysql-5.7.29-linux-glibc2.12-x86_64.tar  /usr/local/mysql/
[root@localhost mysql]# tar -xvf mysql-5.7.29-linux-glibc2.12-x86_64.tar 
[root@localhost mysql]# tar -xvf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz 
[root@localhost mysql]# mv mysql-5.7.29-linux-glibc2.12-x86_64/* /usr/local/mysql/
[root@localhost local]# chown mysql:mysql mysql/
[root@localhost local]# chmod 775 mysql
[root@localhost local]# 
[root@localhost mysql]# chown -R mysql:mysql *
[root@localhost mysql]# chmod -R 775 *

[root@localhost mysql]# echo "export PATH=$PATH:/mysql/bin">>/etc/profile
[root@localhost mysql]# sourct /etc/profile

5 编辑my.cnf文件

MySQL从8.0开始undo 表空间管理已经发生了改变，在5.7版本中一旦MySQL初始化以后，就不能再改变undo表空间了，所以我们在5.7版本中都是在初始化的时候对undo表空间进行一些设置，类似这样：在my.cnf文件中加入innodb_undo_directory= /data/mysql/undologs 和 innodb_undo_tablespaces=5 这两个参数，之所以这么改，是因为我们想把undo表空单独从系统表空间idbdata中分离出来，这样就可以消除因undo的问题造成对ibdata系统表空间的影响，所以上面的参数配置在5.7版本中是我们对MySQL初始化做的一个常规的最佳实践设置，如果不设置，那么在5.7版本中，undo还是默认会放在ibdata中。

对于5的版本注意设置innodb_undo_directory= /data/mysql/undologs 和 innodb_undo_tablespaces=5分离UNDO

以及innodb buff pool的设置，以及字符集的设置

[mysqld]

port=3306

basedir=/mysql/db

datadir=/mysql/data/data1

language=/mysql/db/share/english

server-id=2

log-bin=/mysql/log/mysql-bin

symbolic-links=0

default-storage-engine=INNODB

max_connections=2000

max_connect_errors=10

default_authentication_plugin=mysql_native_password

lower_case_table_names = 1

max_connections = 1000

max_user_connections = 1000

expire_logs_days=15

max_binlog_szie=100M

innodb_undo_directory= /data/mysql/undologs

innodb_undo_tablespaces=5

[mysql]

auto-rehash

[mysqld_safe]

log-error=/mysql/log/mysql.log

pid-file=/mysql/db/run/mysql.pid

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

5 初始化mysql

[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/mysql/data --basedir=/mysql --default-extra-files=/etc/my.cnf

其他初始化方式

$MYSQL_HOME/scripts/mysql_install_db  --datadir=/mysql/data --basedir=/mysql --default-extra-files=/etc/my.cnf

mysqld: Can't change dir to '/usr/local/mysql/data/' (Errcode: 13 - Permission denied)
2020-10-14T06:16:58.734152Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-10-14T06:16:58.737602Z 0 [ERROR] failed to set datadir to /usr/local/mysql/data/
2020-10-14T06:16:58.737626Z 0 [ERROR] Aborting

[root@localhost ~]# mysqld --initialize --user=mysql --explicit_defaults_for_timestamp
mysqld: Can't change dir to '/usr/local/mysql/data/' (Errcode: 13 - Permission denied)
2020-10-14T06:17:09.082052Z 0 [ERROR] failed to set datadir to /usr/local/mysql/data/
2020-10-14T06:17:09.082110Z 0 [ERROR] Aborting

[root@localhost local]# mysqld --initialize --user=mysql --explicit_defaults_for_timestamp
2020-10-14T06:19:17.048887Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-10-14T06:19:17.120919Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-10-14T06:19:17.180295Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 302c22a7-0de5-11eb-a7c6-005056aef032.
2020-10-14T06:19:17.181447Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-10-14T06:19:19.450442Z 0 [Warning] CA certificate ca.pem is self signed.
2020-10-14T06:19:19.605371Z 1 [Note] A temporary password is generated for root@localhost: ZQAMi&jKI3u2

4 配置服务启动

下面简述一下在 Red Hat Linux 系统中的设置方法：
1 、修改 mysql.server ，把它复制到 /etc/rc.d/init.d 目录里面：
# cd /etc/rc.d/init.d
# cp /usr/local/mysql/support-files/mysql.server mysql
2 、接着把它的属性改为“ x ”（ executable ，可执行）
# chmod +x mysql
3 、最后，运行 chkconfig 把 MySQL 添加到你系统的启动服务组里面
去。
# /sbin/chkconfig --del mysql
# /sbin/chkconfig --add mysql

[root@localhost local]# cp /mysql/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost local]# chmod +x /etc/init.d/mysqld 

配置一个my.cnf文件

[mysqld]
server-id=2
port=3306
datadir=/mysql/data/
basedir=/mysql/mysql/
log-bin=/mysql/log/binlog

service mysqld start

[root@mysqldb2 data]# service mysqld start
/etc/init.d/mysqld: line 239: my_print_defaults: command not found
/etc/init.d/mysqld: line 259: cd: /usr/local/mysql: No such file or directory
Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe)

配置文件设置的datadir和basedir存在问题。

service mysqld stop

service mysqld restart

5 登录mysql，修改root密码

[root@localhost local]# mysql -uroot -hlocalhost -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.29

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> 
mysql> 
mysql> 
mysql> alter user 'root'@'localhost' identified by "ora#123";

mysql> create user 'clg'@'%' identified by "ora#123";
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'clg'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select @@version
    -> ;
+-----------+
| @@version |
+-----------+
| 5.7.29    |
+-----------+
1 row in set (0.00 sec)

6 本次部署2台mysql服务器，以下为配置主从复制服务步骤

MySQL的复制至少需要两个MySQL服务，这些MySQL服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。
MySQL的复制（Replication）是一个异步的复制过程，从Master复制到Slave。在Master和Slave中的整个复制过程由3个线程完成，其中两个线程（Sql线程和IO线程）在Slave端，另一个线程（IO线程）在Master端。要实现复制过程，Master必须打开Binary Log功能，复制过程其实就是Slave从Master端获取bin日志，然后在自己服务器上完全顺序执行日志中所记录的各种操作。

主机配置 node1为master，node2位slave。

master端相关复制参数解释

mysql数据库可以指定需要复制到从库的数据库或者表，有时候只需要复制一些用户数据库或者表即可。

vi /etc/my.cnf

replicate_do_table=database.test,database.test2

replicate_ignore_table=database.test,database.test2

replicate_do_db=database

replicate_ignore_db=database1

在node1上面进行配置 

1 启动binlog

2 设置server-id

3 创建复制用户rep1

create user rep1@'%' identified by "ora#123";
grant replication slave on *.* to rep1@'%';

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      688 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

在master服务器上，设置锁定有效，这个操作是为了保障数据库没有操作，以便获取一致性快照

flush tables with read lock;

使用mysqldump备份需要复制的数据库，并在slave端导入。

tar -cvf dat.tar data

unlock tables;
 

在node2执行如下操作

在node2启动数据库时，可以采用--skip-slave-start 来避免启动slave复制。

mysqld_safe ----skip-slave-start &

mysqld_safe ---read-only &

mysql> change master to
    -> master_host='10.228.127.118',
    -> master_user='rep1',
    -> master_password='ora#123',
    -> master_log_file='binlog.000001',
    -> master_log_pos=688;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

    Last_IO_Error: error connecting to master 'rep1@10.228.127.118:3306' - retry-time: 60 retries: 4 message: Can't connect to MySQL server on '10.228.127.118:3306' (113)

可以在master端，执行show processlist查看是否链接。

[root@mysqldb2 data]# telnet 10.228.127.118 3306
Trying 10.228.127.118...
telnet: connect to address 10.228.127.118: No route to host

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.228.127.118
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 688
               Relay_Log_File: mysqldb2-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: binlog.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: 688
              Relay_Log_Space: 533
              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: d4bcb9e7-d498-11eb-9714-005056ae1789
             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.01 sec)

mysql> 

选项解释：

1 log_slave_updates设置从服务器的更新是否写入binlog，默认关闭。

2 master-connect-recry 设置和主服务器丢失链接后，多久进行重连。

3 跳过复制错误号。从库参数。

vi  /etc/my,cnf

slave-skip-errors=1007,1008,1009

如果设置为all，则跳过所有错误。

master查看processlist

同步测试

在master执行如下操作

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table test (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values (2);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> 

查看slave

mysql> desc test
    -> ;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql>