NOTE: This MySQL file is in the/usr/bin directory, and the boot file/etc/init.d/mysql is not a file. Several important directories of MySQL mysql after the installation is done not like SQL Server is installed by default in a directory, its database files, configuration files and command files in different directories, it is important to understand these directories, especially for Linux beginners, because The directory structure of Linux itself is more complex, if you do not know the installation directory of MySQL there is no way to talk about deep learning. Here are some of these directories. 1, database directory  /VAR/LIB/MYSQL/2, configuration file /usr/share/mysql (mysql.server command and configuration file) 3, the related command /usr/bin (mysqladmin Mysqldump and other commands) 4, startup script /etc/rc.d/init.d/(startup script file MySQL directory) modify the login password mysql default no password, installation completed the importance of increasing the password is self-evident. 1. Command usr/bin/mysqladmin-u root password ' new-password ' format: mysqladmin-u user name -p old password password New Password 2, example Example 1: Add a password to root 123456. Type the following command : [[email protected] local]#/usr/bin/mysqladmin-u root password 123456 Note: Because Root does not have a password at the beginning, the-p old password can be omitted. 3, test whether to modify the success 1) without password login [[email protected] local]# mysql error 1045:access denied for user: ' [email pr Otected] ' (Using password:no) display error stating that the password has been modified. 2) Log in with the modified password [[email protected] local]# mysql-u root-P enter Password: (Enter the modified password 123456) welcome to the MySQL Monitor. Commands End With; or \g. your MySQL Connection ID is 4-to-server Version:4.0.16-standard type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer. mysql> Success! This is done by changing the password with the Mysqladmin command, or by modifying the library. Start and stop 1, start mysql after the installation is complete, start the file MySQL in the/ETC/INIT.D directory, run the following command when you need to start. [[email protected] init.d]#/etc/init.d/mysql start2, stop /usr/bin/mysqladmin-u root-p shutdown3, auto start  1) See if MySQL is in the autostart list [[email protected] local]#/sbin/chkconfig –list 2) Add MySQL to your system's Startup service group [[email protected] local]#/sbin/chkconfig– add MySQL 3) Remove MySQL from the Startup service group. [[email protected] local]#/sbin/chkconfig– del mysql change MySQL directory mysql default data file storage directory for/var/lib/ Mysql. If you want to move the directory to/home/data under the following steps: 1, the home directory to establish the data directory cd/home mkdir data2, the MySQL service process to stop: mysqladmin-u root -P shutdown3, put/var/lib/mysql wholeDirectory moved to/home/data mv/var/lib/mysql/home/data/ This moves the MySQL data file to/home/data/mysql 4, locate the MY.CNF configuration file if /etc/directory does not have a my.cnf configuration file, please find the *.cnf file under/usr/share/mysql/, copy one of them to/etc/and rename it to MY.CNF). The command is as follows: [[email protected] mysql]# Cp/usr/share/mysql/my-medium.cnf/etc/my.cnf5, editing the MySQL configuration file/etc/my.cnf & nbsp; To ensure that MySQL works properly, you need to indicate where the Mysql.sock file will be generated. Modify the value of the Socket=/var/lib/mysql/mysql.sock line to the right of the equals sign:/home/mysql/mysql.sock . The operation is as follows: vi my.cnf (edit my.cnf file with VI tool, find the following data modification) # The MySQL server [mysqld] port = 3306 #socket =/var/lib/mysql/mysql.sock (original content, in order to be more secure with "#" comment this line) socket =/home/data/mysql/mysql . Sock (plus this line) 6, modify the MySQL startup script/etc/rc.d/init.d/mysql Finally, you need to modify the MySQL startup script/etc/rc.d/init.d/mysql, which datadir=/var/lib /mysql line, the path to the right of the equal sign is changed to your current actual storage path: Home/data/mysql. [[email protected] etc]# vi/etc/rc.d/init.d/mysql #datadir =/var/lib/mysql (note this line) datadir=/ho Me/data/mysql (plus this line) 7. Restart MySQLService /etc/rc.d/init.d/mysql start Restart Linux with the reboot command if the work moves successfully, otherwise check the previous 7 steps. Common use of MySQL NOTE: Each command in MySQL is followed by a semicolon; 1, display database mysql> show databases; +----------+ | Database | +----------+ | MySQL | | Test | +----------+ 2 rows in Set (0.04 sec) mysql just finished installing two databases: MySQL and test. MySQL library is very important, it has the MySQL system information, we change the password and new users, in fact, the library with the relevant tables to operate.
2, display the database table mysql> use mysql; (open the library, to operate on each library will open this library, similar to foxpro ) database changed mysql > Show tables; +-----------------+ | Tables_in_mysql | +-----------------+ | Columns_priv | | db | | Func | | Host | | Tables_priv | | user | +-----------------+ 6 rows in Set (0.01 sec) 3, display the structure of the data table: describe table name; 4. Display the records in the table: select * FR om table name; Example: Displays the records in the user table in the MySQL library. All users who can operate on the MySQL user are in this table. select * from user;5, build library: create database library name; For example: Create a library with a name of AAA mysql> creation Database aaa;6, build table: use library name; create table table name ( field setting list); For example: Create a table in the AAA library you just created name, the table has ID (ordinal, autogrow), XM (name), XB (gender), CSNY (birth date) four fields use AAA; mysql> CREATE table name (ID int (3) auto_increment NOT null primary key, XM char (8), XB char (2), CSNY date); You can use the describe command to view the table structure you just created. mysql> Describe name; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | ID | Int (3) | | PRI | NULL | auto_increment | | XM | CHAR (8) | YES | | NULL | | | XB | char (2) | YES | | NULL | | | CSNY | Date | YES | | NULL | | +-------+---------+------+-----+---------+----------------+7, add records For example: Add a few related records. mysql> INSERT into name values (' ', ' Zhang San ', ' Male ', ' 1971-10-01 '); mysql> INSERT into name values (' ', ' white Clouds ', ' female ', ' 1972-05-20 '); Use the Select command to verify the results. mysql> select * from name; +----+------+------+------------+ | ID | XM | XB | CSNY | +----+------+------+------------+ | 1 | Zhang San | male | 1971-10-01 | | 2 | Baiyun | female | 1972-05-20 | +----+------+------+------------+8. Change record For example: Change Zhang San's birth date to 1971-01-10 mysql> update name set csny= ' 1971-01-10 ' where xm= ' Zhang San '; 9, delete record For example: delete Zhang San's record. mysql> Delete from name where xm= ' Zhang San '; 10, delete the library and delete table drop database library name; drop table table name; add MySQL user format: Grant Select on database. * to user name @ Login host identified by "password" Example 1, Add a user user_1 password of 123, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command: Mysql> Grant Select,insert,update,delete on * * to [e-mail protected] "%" identified by "123"; Example 1 added user is very dangerous, if you know User_ 1 password, then he can log in to your MySQL database on any computer on the Internet and do whatever you like with your data, see Example 2 for a workaround. Example 2, the addition of a user user_2 password of 123, so that the user can only log on localhost, and the database AAA can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), This allows the user to use the password that knows user_2, and he cannot access the database directly from the Internet, only through the MySQL host to operate the AAA library. Mysql>grant Select,insert,update,delete on aaa.* to [e-mail protected] identified by "123"; With the new user if you can't log in MySQL, log in with the following command: Mysql-u user_1-p-H 192.168.113.50 (-H followed by the IP address to log on to the host) backup and restore 1, Backup for example: Back up the AAA library created in the example to a file back_a AA in [[email protected] root]# Cd/home/data/mysql (go to the library directory, this example library has been transferred from Val/lib/mysql to/home/data/mysql, see part VII above) [[Email Prot Ected] mysql]# mysqldump-u root-p--opt aaa > Back_aaa2, recovery [[email protected] mysql]# mysql-u root-p CCC < BAC K_aaa
Linux installation MySQL database
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
