Install, create, back up, and restore mysql Databases and tables in centos

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!