mysqld --initialize --console
mysqld --verbose --help
初始化密码
MySQL :: MySQL 8.0 Reference Manual :: 2.5.1 Installing MySQL on Linux Using the MySQL Yum Repository https://dev.mysql.com/doc/refman/8.0/en/linux-installation-yum-repo.html
wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm
yum localinstall mysql80-community-release-el7-1.noarch.rpm
[root@d local]# yum localinstall mysql80-community-release-el7-1.noarch.rpm
Loaded plugins: fastestmirror
Examining mysql80-community-release-el7-1.noarch.rpm: mysql80-community-release-el7-1.noarch
Marking mysql80-community-release-el7-1.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql80-community-release.noarch 0:el7-1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==========================================================================================================================================================================================================================
Package Arch Version Repository Size
==========================================================================================================================================================================================================================
Installing:
mysql80-community-release noarch el7-1 /mysql80-community-release-el7-1.noarch 31 k
Transaction Summary
==========================================================================================================================================================================================================================
Install 1 Package
Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mysql80-community-release-el7-1.noarch 1/1
Verifying : mysql80-community-release-el7-1.noarch 1/1
Installed:
mysql80-community-release.noarch 0:el7-1
Complete!
检查mysql版本
[root@d ~]# yum install mysql-community-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.aliyuncs.com
* epel: mirrors.tuna.tsinghua.edu.cn
* extras: mirrors.aliyuncs.com
* updates: mirrors.aliyuncs.com
https://packagecloud.io/rabbitmq/erlang/el/7/x86_64/repodata/repomd.xml: [Errno 14] HTTPS Error 302 - Found
Trying other mirror.
https://packagecloud.io/rabbitmq/erlang/el/7/SRPMS/repodata/repomd.xml: [Errno 14] HTTPS Error 302 - Found
Trying other mirror.
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:8.0.13-1.el7 will be installed
--> Processing Dependency: mysql-community-client(x86-64) >= 8.0.0 for package: mysql-community-server-8.0.13-1.el7.x86_64
--> Running transaction check
---> Package mysql-community-client.x86_64 0:8.0.13-1.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
====================================================================================================================================
Package Arch Version Repository Size
====================================================================================================================================
Installing:
mysql-community-server x86_64 8.0.13-1.el7 mysql80-community 381 M
Installing for dependencies:
mysql-community-client x86_64 8.0.13-1.el7 mysql80-community 26 M
Transaction Summary
====================================================================================================================================
Install 1 Package (+1 Dependent package)
Total download size: 407 M
Installed size: 1.8 G
Is this ok [y/d/N]: y
Downloading packages:
(1/2): mysql-community-client-8.0.13-1.el7.x86_64.rpm | 26 MB 00:00:03
(2/2): mysql-community-server-8.0.13-1.el7.x86_64.rpm | 381 MB 00:00:28
------------------------------------------------------------------------------------------------------------------------------------
Total 14 MB/s | 407 MB 00:00:28
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mysql-community-client-8.0.13-1.el7.x86_64 1/2
Installing : mysql-community-server-8.0.13-1.el7.x86_64 2/2
Verifying : mysql-community-server-8.0.13-1.el7.x86_64 1/2
Verifying : mysql-community-client-8.0.13-1.el7.x86_64 2/2
Installed:
mysql-community-server.x86_64 0:8.0.13-1.el7
Dependency Installed:
mysql-community-client.x86_64 0:8.0.13-1.el7
Complete!
[root@d ~]# yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Community disabled
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Community - disabled
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Community disabled
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Community - disabled
mysql-connectors-community/x86_64 MySQL Connectors Community enabled: 74
mysql-connectors-community-source MySQL Connectors Community - disabled
mysql-tools-community/x86_64 MySQL Tools Community enabled: 74
mysql-tools-community-source MySQL Tools Community - Sourc disabled
mysql-tools-preview/x86_64 MySQL Tools Preview disabled
mysql-tools-preview-source MySQL Tools Preview - Source disabled
mysql55-community/x86_64 MySQL 5.5 Community Server disabled
mysql55-community-source MySQL 5.5 Community Server - disabled
mysql56-community/x86_64 MySQL 5.6 Community Server disabled
mysql56-community-source MySQL 5.6 Community Server - disabled
mysql57-community/x86_64 MySQL 5.7 Community Server disabled
mysql57-community-source MySQL 5.7 Community Server - disabled
mysql80-community/x86_64 MySQL 8.0 Community Server enabled: 49
mysql80-community-source MySQL 8.0 Community Server - disabled
[root@d ~]# mysql
【注意mysql命令源自mysql-community-client的安装，mysqld依赖于mysql-community-server】
开启服务与检查状态
[root@d local]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
[root@d local]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2018-12-21 15:09:24 CST; 29s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 16876 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 16968 (mysqld)
Status: "SERVER_OPERATING"
CGroup: /system.slice/mysqld.service
└─16968 /usr/sbin/mysqld
Dec 21 15:09:13 d systemd[1]: Starting MySQL Server...
Dec 21 15:09:24 d systemd[1]: Started MySQL Server.
[root@d local]#
[xiaole@localhost ~]$ mysql -V
mysql Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)
获取临时命令
grep 'temporary password' /var/log/mysqld.log
[root@d local]# grep 'temporary password' /var/log/mysqld.log
2018-12-21T07:09:19.085037Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: <clk:liPf8oD
注意 用户身份 mysql
# Preconfiguration setup
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
配置数据文件目录、日志目录
[root@d ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@d ~]# mkdir -p /data/mysql/{datadir,log-error}
[root@d ~]# tree /data/mysql/
/data/mysql/
├── datadir
└── log-error
2 directories, 0 files
[root@d ~]#
改变用户组
chown -R mysql:mysql /data/mysql
[root@d local]# cat /data/mysql/log-error/mysqld.log
2018-12-21T09:30:54.455031Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.13) initializing of server in progress as process 16122
2018-12-21T09:31:00.088193Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: kOb&Q1d9;c0/
2018-12-21T09:31:04.036587Z 0 [System] [MY-013170] [Server] /usr/sbin/mysqld (mysqld 8.0.13) initializing of server has completed
2018-12-21T09:31:06.026929Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 16242
2018-12-21T09:31:06.999556Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-12-21T09:31:07.027924Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.13' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
2018-12-21T09:31:07.252867Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
[root@d local]# mysql -uroot
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY '345sdfst!sgA';
Query OK, 0 rows affected (0.03 sec)
注意目录权限
[root@d ~]# tail /data/mysql/log-error/mysqld.log
2018-12-24T02:39:59.743277Z 0 [ERROR] [MY-010273] [Server] Could not create unix socket lock file /var/lib/mysql/mysql.sock.lock.
2018-12-24T02:39:59.743294Z 0 [ERROR] [MY-010268] [Server] Unable to setup unix socket lock file.
2018-12-24T02:39:59.743330Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-12-24T02:40:01.275959Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL.
2018-12-24T02:40:24.043734Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 12363
2018-12-24T02:40:24.701411Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-12-24T02:40:24.702643Z 0 [ERROR] [MY-010273] [Server] Could not create unix socket lock file /var/lib/mysql/mysql.sock.lock.
2018-12-24T02:40:24.702659Z 0 [ERROR] [MY-010268] [Server] Unable to setup unix socket lock file.
2018-12-24T02:40:24.702713Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-12-24T02:40:26.266805Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL.
[root@d ~]# ll -as /var/lib/mysql/
ls: cannot access /var/lib/mysql/: No such file or directory
[root@d ~]# ll -as /var/lib/mysql
ls: cannot access /var/lib/mysql: No such file or directory
[root@d ~]# mkdir /var/lib/mysql
[root@d ~]# chown -R mysql:mysql /var/lib/mysql
[root@d ~]# service mysqld start
日志处理
mysql> show global variables like "%log%";
+--------------------------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------------------------+----------------------------------------+
| activate_all_roles_on_login | OFF |
| back_log | 151 |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_expire_logs_seconds | 2592000 |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_row_metadata | MINIMAL |
| binlog_row_value_options | |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /data/mysql/datadir/d.log |
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_spin_cpu_abs_lwm | 80 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
| innodb_log_write_ahead_size | 8192 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_print_ddl_logs | OFF |
| innodb_redo_log_encrypt | OFF |
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | ON |
| log_bin | ON |
| log_bin_basename | /data/mysql/datadir/binlog |
| log_bin_index | /data/mysql/datadir/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /data/mysql/log-error/mysqld.log |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | ON |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | d-relay-bin |
| relay_log_basename | /data/mysql/datadir/d-relay-bin |
| relay_log_index | /data/mysql/datadir/d-relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | TABLE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /data/mysql/datadir/d-slow.log |
| sql_log_off | OFF |
| sync_binlog | 1 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+--------------------------------------------+----------------------------------------+
77 rows in set (0.00 sec)
mysql>
[root@d mapReduceLogAUTOLocalDB]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
datadir=/data/mysql/datadir
socket=/var/lib/mysql/mysql.sock
log-error=/data/mysql/log-error/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@d mapReduceLogAUTOLocalDB]#
mysql日志详细解析 - KoMiles - 博客园 https://www.cnblogs.com/wangkongming/p/3684950.html
通过sql开启慢查询；
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)
mysql> show global variables like "%slow%";
+---------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /data/mysql/datadir/d-slow.log |
+---------------------------+--------------------------------+
5 rows in set (0.00 sec)
mysql> show global variables like "%long%";
+----------------------------------------------------------+-----------+
| Variable_name | Value |
+----------------------------------------------------------+-----------+
| long_query_time | 10.000000 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_waits_history_long_size | 10000 |
+----------------------------------------------------------+-----------+
5 rows in set (0.00 sec)
pymysql 连接
+----+----------------------+-----------------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------------------+-----------------+------+---------+------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 84 | Waiting on empty queue | NULL |
| 9 | unauthenticated user | connecting host | NULL | Sleep | 26 | login | PLUGIN |
| 10 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+----------------------+-----------------+------+---------+------+------------------------+------------------+
mysql-8.0 安装教程（自定义配置文件，密码方式已修改） - 熊仔其人 - 博客园 https://www.cnblogs.com/xiongzaiqiren/p/8970203.html
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';