MySQL database installation and basic configuration
The CentOS6 and RHEL6 system yum contains the MySQL installation package, which is version MySQL5. The name of the RPM package is mysql-server.
Use the following command to install the MySQL database:
Yum install-y mysql-server MySQL mysql-devel
After the installation is complete, we can start the MySQL service with the following command:
(Service mysqld Start
After starting the MySQL service for the first time, we need to initialize the MySQL database, set the root user's password, and use the following command to set the root user password for MySQL:
mysqladmin-u root password ' password ')
We can connect to the MySQL service on this computer using the following command:
Mysql-u root-p
Boot auto start mysqlchkconfig mysqld on
Config file saved in/etc/my.cnf
SQL Language Basics
The ALTER table statement is used to add, modify, or delete columns in an existing table.
Grammar:
Rename Table name:
ALTER TABLE table_name RENAME table_name_new;
To add columns to a table, use the following syntax:
ALTER TABLE table_name ADD column_name datatype;
To delete a column from a table, use the following syntax:
ALTER TABLE table_name DROP COLUMN column_name;
Note: Some database systems do not allow this method of deleting columns in a database table (DROP column column_name).
To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name MODIFY column_name datatype;
Rename a column:
ALTER TABLE table_name Change COLUMN column_name column_name_new datatype;
The INSERT INTO statement is used to insert a new row into the table.
Insert:
INSERT into table_name values (value 1, value 2,....)
We can also specify the columns for which you want to insert data:
INSERT into table_name (column 1, column 2,...) Values (value 1, value 2,....)
The Update statement is used to modify the data in the table:
UPDATE table name SET column name = new value WHERE Column name = value
The DELETE statement is used to delete rows from the table:
DELETE from table name WHERE column name = value
Delete all rows
You can delete all rows without deleting the table. This means that the structure, properties, and indexes of the table are complete:
Delete from table_name or DELETE * FROM table_name
Create a User:
CREATE USER Monitor identified by ' password ';
To delete a user:
DROP USER Monitor;
Points:
The DROP user cannot automatically close any open user conversations. Also, if the user has an open conversation and cancels the user at this point, the command will not take effect until the user conversation is closed. Once the conversation is closed and the user is canceled, the user will fail to log on again. This is intentionally designed.
To rename a user:
RENAME USER Monitor to Monitor2;
Reset Password:
SET PASSWORD = PASSWORD (' NewPassword ');
SET PASSWORD for monitor = PASSWORD (' NewPassword ');
The permissions granted can be divided into multiple tiers:
Global level database hierarchy Layer level hierarchy sub-program hierarchy
Global level Authorization:
GRANT all privileges on * * to ' monitor ' @ ' percent ' identified by ' password ';
Revoke all authorizations:
REVOKE all privileges, GRANT OPTION from Monitor;
The most widely used backup tool for MySQL is mysqldump.
Use mysqldump for basic database backup recovery.
Back up a database:
Mysqldump-u root-p linuxcast > Linuxcast_backup.sql
Recover a database from a backup file:
Mysql-u Root-p Linuxcast < Linuxcast_backup.sql
Back up the database from one MySQL server to another MySQL server (note that you should first create a database on remote MySQL):
[local-server]# mysqldump-u root-p Linuxcast | MySQL \
-U root-p--host=remote-server-c linuxcast
To view MySQL-supported encodings:
mysql> SHOW CHARACTER SET;
To view the current MySQL server default encoding:
mysql> SHOW VARIABLES like ' character_set% ';
mysql> SHOW VARIABLES like ' collation% ';
Specify the encoding when creating the database:
CREATE DATABASE Linuxcast
DEFAULT CHARACTER SET UTF8
DEFAULT COLLATE utf8_general_ci;
To modify the encoding of an existing database:
ALTER DATABASE linuxcast CHARACTER SET UTF8 COLLATE utf8_general_ci;
(Set the database default encoding:
To modify the MySQL configuration file my.cnf:
[Client]
Default-character-set=utf8
[MySQL]
Default-character-set=utf8
[Mysqld]
Default-character-set = UTF8
Collation-server = Utf8_unicode_ci
init-connect= ' SET NAMES UTF8 '
Character-set-server = UTF8
Restart MySQL after modification is in effect. To solve the problem of not going into MySQL:
/etc/init.d/mysql stop
Mysqld_safe--user=mysql--skip-grant-tables--skip-networking &
Mysql-u root mysqlmysql>update user SET Password=password (' NewPassword ') where user= ' root ';
Mysql>flush privileges;
Mysql>quit/etc/init.d/mysql restart
Mysql-u root-p
Enter Password: < Enter the newly set password newpassword>
Mysql>
To resolve the change password issue:
SET PASSWORD = PASSWORD (' PASSWORD ');
Troubleshoot new users unable to log on: Because the root privilege is too high, create a new user monitor. The creation statement is as follows: GRANT all privileges on * * to ' monitor ' @ '% ' identified by ' password ', where @ "%" is available at any address.
After creation to mysql.user under view, there is the user. However, using mysql-u monitor-p password login, the prompt cannot log in: ERROR 1045 (28000): Access denied for user ' monitor ' @ ' localhost ' (using passwor D:yes)
Workaround:
GRANT all privileges on * * to ' monitor ' @ ' localhost ' identified by ' password ';
Log in with Monitor after exiting, success.
MySQL Simple to use
