Install, create, back up, and restore mysql Databases and tables in centos
1. Create system users and groups that run the MySQL database.
[Root @ beyond ~] # Useradd-M-s/sbin/nologin mysql
Decompress and release the source code:
# Tar zxvf mysql-5.0.56.tar.gz-C/usr/src/
If the configuration cannot be compiled, configure the yum source.
Install the following software;
[Root @ beyond mysql-5.0.56] # yum install * vim *
[Root @ beyond mysql-5.0.56] # yum install-y * termcap *
Configure compilation;
[Root @ beyond mysql-5.0.56] #./configure -- prefix =/usr/local/apache2
Remember to check the platform specific part of the reference manual
Hints about installing MySQL on your platform. Also have a look at
Files in the Docs directory.
Thank you for choosing MySQL!
Compile and install;
[Root @ beyond mysql-5.0.56] # make & make install
2. Create a configuration file;
[Root @ beyond mysql-5.0.56] # cp support-files/my-medium.cnf/etc/my. cnf
Initialize the database;
[Root @ beyond mysql-5.0.56] #/usr/local/mysql/bin/mysql_install_db -- user = mysql
Modify the ownership of related directories so that mysql users can read and write databases.
[Root @ beyond mysql-5.0.56] # chown-R root. mysql/usr/local/mysql/
[Root @ beyond mysql-5.0.56] # chown-R mysql/usr/local/mysql/
Adjust the lib library path:
[Root @ beyond mysql-5.0.56] # echo "/usr/local/mysql/lib/mysql">/etc/ld. so. conf
Refresh the library file search path to make the modification take effect
[Root @ beyond mysql-5.0.56] # ldconfig
MySQL startup control;
[Root @ beyond mysql-5.0.56] #/usr/local/mysql/bin/mysqld_safe -- user = mysql &
[Root @ beyond mysql-5.0.56] # netstat-ntpl | grep 3306
Tcp 0 0 0.0.0.0: 3306 0.0.0.0: * LISTEN 23437/mysqld
Add mysqld as a system service;
[Root @ beyond mysql-5.0.56] # cp support-files/mysql. server/etc/init. d/mysqld
[Root @ beyond mysql-5.0.56] # chmod + x/etc/init. d/mysqld
[Root @ beyond mysql-5.0.56] # chkconfig -- add mysqld
[Root @ beyond mysql-5.0.56] # chkconfig mysqld on
Set the execution path of the MySQL program;
[Root @ beyond mysql-5.0.56] # export PATH = $ PATH:/usr/local/mysql/bin/
[Root @ beyond mysql-5.0.56] # echo "PATH = $ PATH:/usr/local/mysql/bin">/etc/profile
Basic database management, login and exit MySQL Environment
[Root @ beyond ~] # Mysql-u root
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 1
Server version: 5.0.56-log Source distribution exit; exit
Mysql>
Set the mysql database password of the root user;
[Root @ beyond ~] # Mysqladmin-u root password "123.com"
[Root @ beyond ~] # Mysql-u root-p
Enter password:
Database structure,
// Query database commands:
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Test |
+ -------------------- +
3 rows in set (0.00 sec)
Query data tables in a database;
Mysql> USE mysql;
Database changed
Mysql> show tables;
+ --------------------------- +
| Tables_in_mysql |
+ --------------------------- +
| Columns_priv |
| Db |
| Func |
| Help_category |
| Help_keyword |
| Help_relation |
| Help_topic |
| Host |
| Proc |
| Procs_priv |
| Tables_priv |
| Time_zone |
| Time_zone_leap_second |
| Time_zone_name |
| Time_zone_transition |
| Time_zone_transition_type |
| User |
+ --------------------------- +
17 rows in set (0.00 sec)
Create and delete databases;
Mysql> create database beyond;
Query OK, 1 row affected (0.00 sec)
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Beyond |
| Mysql |
| Test |
+ -------------------- +
4 rows in set (0.00 sec)
Delete a database;
Mysql> drop database taotao;
Query OK, 0 rows affected (0.00 sec)
Create and delete data tables;
Mysql> create table songs (songs_name CHAR (30) not null, songs_passwd CHAR (20) not null default '123. com ', primary key (songs_name ));
Query OK, 0 rows affected (0.02 sec)
Insert a new data record;
Mysql> insert into auth. users (user_name, user_passwd) VALUES ('huangjiajv', ENCRYPT ('123 '));
Query OK, 1 row affected (0.01 sec)
Mysql> insert into auth. users (user_name, user_passwd) VALUES ('huangguanzhong ', ENCRYPT ('com. 123 '));
Query OK, 1 row affected (0.00 sec)
Modify data table information;
Mysql> UPDATE auth. users SET user_passwd = ENCRYPT ('2017. com') WHERE user_name = 'taotao ';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
Database backup and recovery;
[Root @ www ~] # Mysqldump-u root-p auth> mysql-auth. SQL
Enter password:
[Root @ www ~] # Ll mysql-auth. SQL
-Rw-r -- 1 root 1863 Dec 30 0:01 mysql-auth. SQL // backup database;
Delete database auth;
Mysql> USE mysql;
Database changed
Mysql> drop database auth;
Query OK, 1 row affected (0.01 sec)
Check whether the file is deleted;
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Beyond |
| Mysql |
| Test |
+ -------------------- +
4 rows in set (0.00 sec)
[Root @ www ~] # Mysql-u root-p auth <mysql-auth. SQL
Enter password:
[Root @ www ~] # Mysqldump-u root-p beyond>/usr/src/mysql-beyond. SQL
Enter password:
[Root @ www ~] # Ll/usr/src/mysql-beyond. SQL
-Rw-r -- 1 root 1775 Dec 30/usr/src/mysql-beyond. SQL
[Root @ www ~] # Mysql-u root-p beyond </usr/src/mysql-beyond. SQL
Enter password:
[Root @ www ~] #
Back up a table in a database;
[Root @ www ~] # Mysqldump-u root-p mysql host user> mysql. host-user. SQL
Enter password:
[Root @ www ~] #
Just installed mysql
Sudo apt-get install mysql
The installation is successful, and the password and OK are required at the end of the installation.
Mysql-uroot-p
Enter the password.
An error occurred!
ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: YSE)
Ask a friend, he said that the initial password is empty, but I named the password.
Leave the password Blank
Or error!
ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: NO)
Change the password!
# Sudo/etc/init. d/mysql stop
# Mysqld_safe -- user = mysql -- skip-grant-tables -- skip-networking &
# Mysql-u root mysql
Mysql> 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:
Mysql>
Done!