本次安装使用的MySQL安装包如下,是从Oracle官方下载的最新MySQL 5.7企业版二进制安装包。
mysql-advanced-5.7.39-el7-x86_64.tar.gz
将安装包上传到/root目录下并解压。
---解压
# tar -xzf mysql-advanced-5.7.39-el7-x86_64.tar.gz
---重命名
# mv mysql-advanced-5.7.39-el7-x86_64 mysql
---移动目录
# mv mysql /usr/local
创建mysql用户和组
---创建数据目录
# mkdir /data/mysql
---创建组
# groupadd mysql
---创建用户
# useradd -d /data/mysql -g mysql mysql
---目录授权
# chown -R mysql.mysql /data/mysql /usr/local/mysql
初始化MySQL数据库
# cd /usr/local/mysql/bin
---初始化mysql,最后一行的最后一个随机密码(n;vazSiq7BGX),即为mysql数据库root用户的初始化密码
# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql
2022-07-26T01:47:36.906037Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-26T01:47:37.099652Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-26T01:47:37.127416Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-26T01:47:37.190697Z 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: ed209b51-0c84-11ed-91e5-000c29a21bcf.
2022-07-26T01:47:37.191600Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-26T01:47:37.542126Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-26T01:47:37.542141Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-26T01:47:37.542916Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-26T01:47:37.617164Z 1 [Note] A temporary password is generated for root@localhost: n;vazSiq7BGX
/etc/my.cnf 配置
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql
symbolic-links=0
[mysql_safe]
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
skip_name_resolve
skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 4M
table_open_cache = 256
sort_buffer_size = 1M
join_buffer_size = 16M
net_buffer_length = 8K
read_buffer_size = 1M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 16M
thread_cache_size = 32
query_cache_size = 32M
query_cache_limit = 4M
tmp_table_size = 64M
transaction_isolation = REPEATABLE-READ
thread_stack = 512K
wait_timeout = 10
explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
back_log = 300
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_data_home_dir = /data/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql
innodb_buffer_pool_size = 2048M
innodb_thread_concurrency = 16
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
long_query_time = 2
log-queries-not-using-indexes
character-set-server=utf8mb4
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
default-character-set=utf8mb4
no-auto-rehash
[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 1M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
备注:以上配置参数只作为示例,实际环境的配置,请以MySQL数据库DBA的建议为准!
配置环境变量
---修改~/.bash_profile,在PATH字段种添加mysql的bin目录
PATH=$JAVA_HOME/bin:$PATH:/usr/local/mysql/bin:/usr/local/bin:$HOME/bin
export PATH
---保存后退出执行source
# source .bash_profile
配置、启动MySQL服务
# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# chkconfig --add mysqld
# systemctl start mysqld
# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
Active: active (running) since 二 2022-07-26 10:23:21 CST; 5s ago
Docs: man:systemd-sysv-generator(8)
Process: 4021 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
Tasks: 28
Memory: 170.4M
CGroup: /system.slice/mysqld.service
├─4032 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/www.myseafile.org.pid
└─4198 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=www.myseafile.org.err --pid-file=/data/mysql...
7月 26 10:23:20 www.myseafile.org systemd[1]: Starting LSB: start and stop MySQL...
7月 26 10:23:21 www.myseafile.org mysqld[4021]: Starting MySQL.Logging to '/data/mysql/www.myseafile.org.err'.
7月 26 10:23:21 www.myseafile.org mysqld[4021]: SUCCESS!
7月 26 10:23:21 www.myseafile.org systemd[1]: Started LSB: start and stop MySQL.
---3306端口启动检查
# netstat -tnlp| grep 3306
tcp6 0 0 :::3306 :::* LISTEN 4198/mysqld
修改默认随机生成的root用户密码
# mysqladmin -uroot -p'n;vazSiq7BGX' password 'YourPassword'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
使用修改后的密码登录到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.39-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2022, 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> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql>
创建数据库、表、用户、给用户赋予数据库表操作权限的简单操作示例
mysql> create database mydbs;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydbs |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use mydbs;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> show create database mydbs;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| mydbs | CREATE DATABASE `mydbs` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database mydbs character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show create database mydbs;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| mydbs | CREATE DATABASE `mydbs` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table employee(id INT, name char(20), sex char(4), age INT, department char(60));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_mydbs |
+-----------------+
| employee |
+-----------------+
1 row in set (0.00 sec)
mysql> show create table employee;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
`id` int(11) DEFAULT NULL,
`name` char(20) DEFAULT NULL,
`sex` char(4) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`department` char(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> select * from employee;
Empty set (0.00 sec)
mysql> describe employee;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| department | char(60) | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select password('YOUR_PASSWORD');
+-------------------------------------------+
| password('YOUR_PASSWORD') |
+-------------------------------------------+
| *5C73556E2ECF5153E6917DFEB894DE5B777543D9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> CREATE USER 'employee_admin'@'%' IDENTIFIED BY PASSWORD '*5C73556E2ECF5153E6917DFEB894DE5B777543D9';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> GRANT SELECT,INSERT,DELETE,UPDATE,ALTER ON mydbs.* TO 'employee_admin'@'%' IDENTIFIED BY PASSWORD '*5C73556E2ECF5153E6917DFEB894DE5B777543D9' WITH GRANT OPTION;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql>
下面我们使用WebLogic中间件的数据源配置,来测试下数据库用户的使用是否正常。
配置数据源的参数信息
测试数据库连接成功,因此我们上面创建的数据库、用户等,都可以正常使用了。