Centos 6.3 MySQL installation and setup
# yum install mysql mysql-devel mysql-server
# /etc/init.d/mysql start
# /usr/bin/mysql_secure_installation
If it prompt for password. you can try empty password or "password"
Then setup according to what you want it be
Login using
# mysql -u root -p
For Amazon linux
If you no using RDS and use mysql on server itself.
after install mysql server, originally it didnt come with database and root users
you need to start run the mysqld services to let it build the first database
# /etc/init.d/mysqld start
then you need to stop it so we can use the safe mode
# /etc/init.d/mysqld stop
# mysqld_safe --skip-grant-tables &
This will start in safe mode, you can access mysql without password
# mysql -u root
mysql> insert into user (Host, User, Password) values ('localhost','root','');
mysql> update user set Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',Create_user_priv='Y',Event_priv='Y' ,Trigger_priv='Y' ,Create_tablespace_priv='Y' where user='root';
This will help to create the root users
then you can exit it and close all safe mode
# killall mysql_safe
# /etc/init.d/mysqld on
you can login the console in normal mode
# mysql -u root
mysql> grant all privileges on *.* to 'root'@'localhost' with grant option;
then exit it and update the root password
# mysqladmin -u root password NEWPASSWORD
Manage Databases
create database
# mysql> create database [databasename];
delete database
# mysql> drop database [databasename];
show all database
mysql> show databases;
show database's fiels formats
mysql> describe [databasename];
switch to database
mysql> use [databasename];
Manage Tables
Show all tables in database
mysql> show tables;
show all data in table
mysql> SELECT * FROM [table]
show info from selected rows with value "Name"
mysql> SELECT * FROM [table] Where [field] = "Name";
Manage Users
Create users
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
if you want to allow access from anywhere for that user, you can use "%" wildcard to replace the localhost
mysql> CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
Grant Privileges to users
mysql> GRANT ALL PRIVILEGES ON * . * TO '
newuser'@'localhost';
mysql> FLUSH PRIVILEGES;
Extra privileges information
- ALL PRIVILEGES- as we saw previously, this would allow a MySQL
user all access to a designated database (or if no database is selected,
across the system)
- CREATE- allows them to create new tables or databases
- DROP- allows them to them to delete tables or databases
- DELETE- allows them to delete rows from tables
- INSERT- allows them to insert rows into tables
- SELECT- allows them to use the Select command to read through databases
- UPDATE- allow them to update table rows
- GRANT OPTION- allows them to grant or remove other users' privileges
Example:
GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;
Remove users privileges
REVOKE [type of permission] ON [database name].[table name] TO ‘[username]’@‘localhost’;
delete user
mysql> DROP USER ‘demo’@‘localhost’;
Migrate database
Before migrating, please check the /etc/my.conf to check what engine it use.
you need to set it to the new database before you import in the data
backup database out
# mysqldump -u root -p [database] > database.sql
backup all database
# mysqldump -u root -p --all-databases > alldb.sql
restore database
# mysql -u root -p [database] < database.sql
restore all database
# mysql -u root -p < alldb.sql
backup database out for innodb
if you face this error message
Access denied for user ‘root’@’localhost’ to database ‘testing’ when using LOCK TABLES
you can try add --single-transaction
example
# mysqldump -u root -p --single-transaction [database] > database.sql
Other / Troubleshoot
ERROR 1040 (00000): Too many connections
check how many process list
mysql> show processlist;
and increase the parameter at my.cnf
max_connections = 500
---------------------------------------------------------------
If you fresh install mysql, the root password is either is empty or had set temp password.
for temp password, check this
grep 'temporary password' /var/log/mysqld.log
-----------------------------------------------
After login, it need you to reset the password, some mysql implement some requirement on its password, use this command to check
SHOW VARIABLES LIKE 'validate_password%';
if you want to update it, use below example command
SET GLOBAL validate_password_length = 6;
SET GLOBAL validate_password_number_count = 0;
reference:
https://dev.mysql.com/doc/refman/8.0/en/validate-password-options-variables.html
-----------------------------------------------
below command is to set the user use back the native password auth
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
