MySQL setup for centos 6.3 or amazon linux and simple command

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>';