CentOS 安装 MySQL

Install

1. Visit official site, download rpm file.

   wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm2
   

2. Install rpm package:

   sudo rpm -ivh mysql57-community-release-el7-9.noarch.rpm
   

3. Install MySQL:

   sudo yum install mysql-server
   

4. Start MySQL:

   sudo systemctl start mysqld
   sudo systemctl status mysqld
   sudo systemctl restart mysqld
   

5. grep initial root password:

   sudo grep password /var/log/mysqld.log
   
   # it will show
   A temporary password is generated for root@localhost: ?VesqAu9sunR
   

Reference:

Maintenance

# show all database
show databases;

# show all user permission
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS users FROM mysql.user;

# add user
# user: test  password: test
create user test identified by 'test';

# show grants
show grants for 'test';

# grant
grant all privileges on *.* to test@'%' identified by 'test';

# flush
flush privileges;

#############################################
#############################################

SET GLOBAL time_zone = '+8:00';
SET time_zone = '+8:00';
FLUSH PRIVILEGES;

Debug

• sudo systemctl start mysql failed

• sudo tail -30 /var/log/mysql/error.log shows:

  2019-07-20T10:14:38.749909Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
  2019-07-20T10:14:38.749916Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
  2019-07-20T10:14:38.749920Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
  2019-07-20T10:14:38.749929Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
  2019-07-20T10:14:38.749932Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
  2019-07-20T10:14:38.749935Z 0 [ERROR] Failed to initialize builtin plugins.
  2019-07-20T10:14:38.749938Z 0 [ERROR] Aborting

• solution

  sudo vim /etc/mysql/my.cnf
  
  # add:
  innodb_buffer_pool_size = 20M
  
  # then
  sudo systemctl start mysql
  

rollback freeze

# SELECT * FROM `PROCESSLIST`
# SELECT * FROM information_schema.`PROCESSLIST`
SELECT * FROM information_schema.PROCESSLIST

# 42 is the `ID` from result which is hanging
KILL 42

for macOS

brew install mysql

...

We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start

for Ubuntu

vim /etc/mysql/my.cnf
vim /etc/mysql/conf.d/mysql.cnf

[mysqld]
#key_buffer_size=4MB
#query_cache_size=4MB
#innodb_buffer_pool_size=4MB
#innodb_log_buffer_size=4MB

#tmp_table_size=4MB
#max_connections=50

table_definition_cache=400