系统运维——mysql 企业级配置

最新推荐文章于 2024-09-29 16:49:58 发布

TYW0823

最新推荐文章于 2024-09-29 16:49:58 发布

阅读量1.1k

点赞数 16

CC 4.0 BY-SA版权

分类专栏：系统运维文章标签： mysql 数据库运维

本文链接：https://blog.csdn.net/TYW0823/article/details/141591633

实验环境

mysql-node2

[root@localhost ~]# hostnamectl set-hostname mysql-node2.timinglee.org
[root@localhost ~]# exit
logout

Connection closed.

Disconnected from remote host(172.25.254.140:22) at 11:39:30.

Type `help' to learn how to use Xshell prompt.
[C:\~]$ ssh [email protected]


Connecting to 172.25.254.140:22...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.

WARNING! The remote SSH server rejected X11 forwarding request.
Last login: Thu Aug 22 11:37:40 2024 from 172.25.254.1
[root@mysql-node2 ~]# nmcli connection modify ens33 ipv4.addresses 172.25.254.20/24 ipv4.method manual connection.autoconnect yes
[root@mysql-node2 ~]# ifconfig 
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.25.254.140  netmask 255.255.255.0  broadcast 172.25.254.255
        inet6 fe80::da8c:dc95:8517:bfff  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:57:fc:c4  txqueuelen 1000  (Ethernet)
        RX packets 348  bytes 31686 (30.9 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 223  bytes 25211 (24.6 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

改IP。

[root@mysql-node2 ~]# nmcli connection modify ens33 ipv4.addresses 172.25.254.20/24 ipv4.method manual connection.autoconnect yes

[root@mysql-node2 ~]# nmcli connection up ens33 

Socket error Event: 32 Error: 10053.
Connection closing...Socket close.

Connection closed by foreign host.

Disconnected from remote host(172.25.254.140:22) at 15:34:09.

Type `help' to learn how to use Xshell prompt.
[C:\~]$ ssh [email protected]


Connecting to 172.25.254.20:22...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.

WARNING! The remote SSH server rejected X11 forwarding request.
Last login: Thu Aug 22 11:39:54 2024 from 172.25.254.1
[root@mysql-node2 ~]#

挂载。

[root@mysql-node2 ~]# mount /dev/sr0 /mnt/
mount: /dev/sr0 is write-protected, mounting read-only

下载依赖。

[root@mysql-node2 ~]# yum install cmake gcc-c++ openssl-devel ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el9_4.x86_64.rpm rpcgen.x86_64

Installed:
  cmake.x86_64 0:2.8.12.2-2.el7                gcc-c++.x86_64 0:4.8.5-44.el7       
  ncurses-devel.x86_64 0:5.9-14.20130511.el7_4 openssl-devel.x86_64 1:1.0.2k-19.el7

传包。

[root@mysql-node1 ~]# ls
anaconda-ks.cfg                           mysql-5.7.44
libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm  mysql-boost-5.7.44.tar.gz
[root@mysql-node1 ~]# yum install libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm -y

安装 mysql 。

[root@mysql-node1 ~]# ls
anaconda-ks.cfg	libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm  mysql-boost-5.7.44.tar.gz
[root@mysql-node2 ~]# tar zxf mysql-boost-5.7.44.tar.gz 
[root@mysql-node2 ~]# ls
anaconda-ks.cfg  mysql-5.7.44  mysql-boost-5.7.44.tar.gz
[root@mysql-node2 ~]# cd mysql-5.7.44/
[root@mysql-node2 mysql-5.7.44]#

检测。

[root@mysql-node2 mysql-5.7.44]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/

编译。

[root@mysql-node2 mysql-5.7.44]# make -j2
[root@mysql-node2 mysql-5.7.44]# make install

[root@mysql-node2 mysql-5.7.44]# cd /usr/local/mysql/
[root@mysql-node2 mysql]# ls
bin   include  LICENSE  mysql-test  README-test  support-files
docs  lib      man      README      share
[root@mysql-node2 mysql]# useradd -s /sbin/nologin -M mysql
[root@mysql-node2 mysql]# mkdir /data/mysql -p
[root@mysql-node2 mysql]# chown mysql.mysql -R /data/mysql/

启动脚本。

[root@mysql-node2 mysql]# pwd
/usr/local/mysql
[root@mysql-node2 mysql]# ls
bin   include  LICENSE  mysql-test  README-test  support-files
docs  lib      man      README      share
[root@mysql-node2 mysql]# cd support-files/
[root@mysql-node2 support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@mysql-node2 support-files]# vim mysql.server

[root@mysql-node2 support-files]# cp mysql.server /etc/init.d/mysqld

进配置文件写日志。

[root@mysql-node2 support-files]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0

[root@mysql-node2 support-files]# ls /data/mysql/
[root@mysql-node2 support-files]#

[root@mysql-node2 support-files]# ll /usr/local/mysql/bin/
total 1059020
-rwxr-xr-x. 1 root root   6413584 Aug 22 12:00 innochecksum
-rwxr-xr-x. 1 root root    611920 Aug 22 11:55 lz4_decompress
-rwxr-xr-x. 1 root root   8469448 Aug 22 12:00 myisamchk
-rwxr-xr-x. 1 root root   8020712 Aug 22 12:00 myisam_ftdump
-rwxr-xr-x. 1 root root   7584528 Aug 22 12:00 myisamlog
-rwxr-xr-x. 1 root root   8147880 Aug 22 12:00 myisampack
-rwxr-xr-x. 1 root root   5537376 Aug 22 11:56 my_print_defaults
-rwxr-xr-x. 1 root root   8349832 Aug 22 12:08 mysql
-rwxr-xr-x. 1 root root   7016864 Aug 22 12:08 mysqladmin
-rwxr-xr-x. 1 root root   8955760 Aug 22 12:08 mysqlbinlog
-rwxr-xr-x. 1 root root   7307808 Aug 22 12:08 mysqlcheck
-rwxr-xr-x. 1 root root   8963568 Aug 22 12:08 mysql_client_test
-rwxr-xr-x. 1 root root 202554416 Aug 22 12:08 mysql_client_test_embedded
-rwxr-xr-x. 1 root root      5238 Aug 22 11:54 mysql_config
-rwxr-xr-x. 1 root root   5703384 Aug 22 12:08 mysql_config_editor
-rwxr-xr-x. 1 root root 247378728 Aug 22 12:08 mysqld
-rwxr-xr-x. 1 root root     27038 Aug 22 11:54 mysqld_multi
-rwxr-xr-x. 1 root root     27859 Aug 22 11:54 mysqld_safe
-rwxr-xr-x. 1 root root   7375200 Aug 22 12:08 mysqldump
-rwxr-xr-x. 1 root root      7844 Aug 22 11:54 mysqldumpslow
-rwxr-xr-x. 1 root root 201741784 Aug 22 12:08 mysql_embedded
-rwxr-xr-x. 1 root root   7010824 Aug 22 12:08 mysqlimport
-rwxr-xr-x. 1 root root   8281016 Aug 22 12:08 mysql_install_db
-rwxr-xr-x. 1 root root   5597912 Aug 22 12:08 mysql_plugin
-rwxr-xr-x. 1 root root  16822472 Aug 22 12:08 mysqlpump
-rwxr-xr-x. 1 root root   6959456 Aug 22 12:08 mysql_secure_installation
-rwxr-xr-x. 1 root root   6968472 Aug 22 12:08 mysqlshow
-rwxr-xr-x. 1 root root   7066168 Aug 22 12:08 mysqlslap
-rwxr-xr-x. 1 root root   5973416 Aug 22 11:56 mysql_ssl_rsa_setup
-rwxr-xr-x. 1 root root   7925424 Aug 22 12:08 mysqltest
-rwxr-xr-x. 1 root root 201155360 Aug 22 12:08 mysqltest_embedded
-rwxr-xr-x. 1 root root   5158200 Aug 22 11:56 mysql_tzinfo_to_sql
-rwxr-xr-x. 1 root root  10570088 Aug 22 12:08 mysql_upgrade
-rwxr-xr-x. 1 root root  22343048 Aug 22 12:08 mysqlxtest
-rwxr-xr-x. 1 root root   5679224 Aug 22 12:00 perror
-rwxr-xr-x. 1 root root   5334880 Aug 22 11:56 replace
-rwxr-xr-x. 1 root root   5537048 Aug 22 11:56 resolveip
-rwxr-xr-x. 1 root root   5610112 Aug 22 11:56 resolve_stack_dump
-rwxr-xr-x. 1 root root    160088 Aug 22 11:55 zlib_decompress

修改环境变量。

[root@mysql-node2 support-files]# vim ~/.bash_profile 

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

export PATH

[root@mysql-node2 support-files]# source ~/.bash_profile

初始化。

[root@mysql-node2 support-files]# ll /data/mysql/ -d
drwxr-xr-x. 2 mysql mysql 6 Aug 22 13:51 /data/mysql/
[root@mysql-node2 support-files]# mysqld --user mysql --initialize
2024-08-22T05:58:53.178397Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-22T05:58:53.339965Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-22T05:58:53.366416Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-22T05:58:53.420686Z 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: 9c613d24-604b-11ef-b0b3-000c2957fcc4.
2024-08-22T05:58:53.421538Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-22T05:58:53.498179Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-22T05:58:53.498196Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-22T05:58:53.498680Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-22T05:58:53.528950Z 1 [Note] A temporary password is generated for root@localhost: zFimd/EX/9AE

初始密码为：

zFimd/EX/9AE

如果初始化过程中出现问题，执行以下命令后重新初始化。

[root@mysql-node2 support-files]# rm -rf /data/mysql/*

设置开机自启动。

[root@mysql-node2 support-files]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-node2.timinglee.org.err'.
 SUCCESS! 
[root@mysql-node2 support-files]# yum install chkconfig -y

[root@mysql-node2 support-files]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

netconsole     	0:off	1:off	2:off	3:off	4:off	5:off	6:off
network        	0:off	1:off	2:on	3:on	4:on	5:on	6:off
rhnsd          	0:off	1:off	2:on	3:on	4:on	5:on	6:off

[root@mysql-node2 support-files]# chkconfig mysqld on
[root@mysql-node2 support-files]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld         	0:off	1:off	2:on	3:on	4:on	5:on	6:off
netconsole     	0:off	1:off	2:off	3:off	4:off	5:off	6:off
network        	0:off	1:off	2:on	3:on	4:on	5:on	6:off
rhnsd          	0:off	1:off	2:on	3:on	4:on	5:on	6:of

安全初始化。

[root@mysql-node2 ~]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: no
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : no

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y     
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

登录数据库。

[root@mysql-node2 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.44 Source distribution

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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 |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> quit;
Bye
[root@mysql-node2 ~]#

停止20数据库，同步10。

[root@mysql-node2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!

mysql-node1

[root@mysql-node10 ~]# hostnamectl set-hostname mysql-node1.timinglee.org
[root@mysql-node10 ~]# exit
logout

Connection closed.

Disconnected from remote host(172.25.254.10:22) at 15:51:17.

Type `help' to learn how to use Xshell prompt.
[C:\~]$ ssh [email protected]


Connecting to 172.25.254.10:22...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.

WARNING! The remote SSH server rejected X11 forwarding request.
Last login: Thu Aug 22 09:27:01 2024 from 172.25.254.1
[root@mysql-node1 ~]# ifconfig 
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.25.254.10  netmask 255.255.255.0  broadcast 172.25.254.255
        inet6 fe80::929:2f3f:51e2:42d2  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:37:a6:56  txqueuelen 1000  (Ethernet)
        RX packets 372  bytes 34024 (33.2 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 238  bytes 26261 (25.6 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 4  bytes 340 (340.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 4  bytes 340 (340.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@mysql-node1 ~]#

[root@mysql-node1 ~]# mount /dev/sr0 /mnt/
mount: /dev/sr0 is write-protected, mounting read-only
[root@mysql-node1 ~]# yum install rsync.x86_64 -y

下载依赖。

[root@mysql-node1 ~]# yum install cmake gcc-c++ openssl-devel \
>  ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el9_4.x86_64.rpm rpcgen.x86_64

[root@mysql-node1 mysql-5.7.44]# yum install openssl-devel ncurses-devel libncurses5-dev libtirpc-devel -y

[root@mysql-node1 ~]# ls
anaconda-ks.cfg  mysql-boost-5.7.44.tar.gz
[root@mysql-node1 ~]# tar zxf mysql-boost-5.7.44.tar.gz 
[root@mysql-node1 ~]# ls
anaconda-ks.cfg  mysql-5.7.44  mysql-boost-5.7.44.tar.gz
[root@mysql-node1 ~]# cd mysql-5.7.44/
[root@mysql-node1 mysql-5.7.44]#

[root@mysql-node1 mysql-5.7.44]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/

[root@mysql-node1 mysql-5.7.44]# make -j2
[root@mysql-node1 mysql-5.7.44]# make install

10添加用户。

[root@mysql-node10 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql-node10 ~]# mkdir -p /data/mysql
[root@mysql-node10 ~]# chown mysql.mysql /data/mysql/

20传数据。

[root@mysql-node2 ~]# rsync -al -r mysql [email protected]:/usr/local/
-bash: rsync: command not found
[root@mysql-node2 ~]# yum search rsync
Loaded plugins: product-id, search-disabled-repos, subscription-manager

This system is not registered with an entitlement server. You can use subscription-manager to register.

================================ N/S matched: rsync ================================
rsync.x86_64 : A program for synchronizing files over a network

  Name and summary matches only, use "search all" for everything.
[root@mysql-node2 ~]# yum install rsync.x86_64 -y

[root@mysql-node2 ~]# cd /usr/local/
[root@mysql-node2 local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src
[root@mysql-node2 local]# rsync -al -r mysql [email protected]:/usr/local/
[email protected]'s password:

查看10数据是否传输成功。

[root@mysql-node1 ~]# cd /usr/local/
[root@mysql-node1 local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src
[root@mysql-node1 local]# du -sh mysql/
1.9G	mysql/
[root@mysql-node1 local]# cd mysql/
[root@mysql-node1 mysql]# ls
bin   include  LICENSE  mysql-test  README-test  support-files
docs  lib      man      README      share

[root@mysql-node1 mysql]# cp support-files/mysql.server /etc/init.d/mysqld

改配置文件。

[root@mysql-node1 mysql]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0

[root@mysql-node1 mysql]# vim ~/.bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

export PATH

[root@mysql-node1 mysql]# source ~/.bash_profile

初始化。

[root@mysql-node1 mysql]# mysqld --user mysql --initialize
2024-08-22T08:08:28.482223Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see docum