centos下mysql数据库安装、创建库和表，备份还原
时间:2015-04-12 20:35 来源:linux.it.net.cn 作者:IT
一、创建运行MySQL数据库的系统用户、组。
[root@beyond ~]# useradd -M -s /sbin/nologin mysql
解压并释放源代码：
[root@beyond mnt]# tar zxvf mysql-5.0.56.tar.gz -C /usr/src/
要是配置编译不了，就配置yum源
安装下面的软件；
[root@beyond mysql-5.0.56]# yum install *vim*
[root@beyond mysql-5.0.56]# yum install -y *termcap*
配置编译；
[root@beyond mysql-5.0.56]# ./configure --prefix=/usr/local/apache2
Remember to check the platform specific part of the reference manual for
hints about installing MySQL on your platform. Also have a look at the
files in the Docs directory.
Thank you for choosing MySQL!
编译并安装；
[root@beyond mysql-5.0.56]# make && make install
2.建立配置文件；
[root@beyond mysql-5.0.56]# cp support-files/my-medium.cnf /etc/my.cnf
初始化数据库；
[root@beyond mysql-5.0.56]# /usr/local/mysql/bin/mysql_install_db --user=mysql
修改相关目录的所有权，以便mysql用户可以读写数据库。
[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/
调整lib库路径：
[root@beyond mysql-5.0.56]# echo "/usr/local/mysql/lib/mysql" >> /etc/ld.so.conf
刷新库文件搜索路径，使修改生效
[root@beyond mysql-5.0.56]# ldconfig
MySQL启动控制；
[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
将mysqld添加为系统服务；
[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
设置MySQL程序的执行路径；
[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
数据库的基本管理，登录及退出MySQL环境
[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
mysql>
设置root用户的mysql数据库密码；
[root@beyond ~]# mysqladmin -u root password "123.com"
[root@beyond ~]# mysql -u root -p
Enter password:
数据库结构，
//查询数据库的命令：
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
查询数据库中的数据表；
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)
数据库的创建和删除；
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)
删除数据库；
mysql> DROP DATABASE taotao;
Query OK, 0 rows affected (0.00 sec)
创建和删除数据表；
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)
插入新的数据记录；
mysql> INSERT INTO auth.users(user_name,user_passwd) VALUES('huangjiajv',ENCRYPT('123456'));
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)
修改数据表信息；
mysql> UPDATE auth.users SET user_passwd=ENCRYPT('123.com') WHERE user_name='taotao';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
数据库的备份和恢复；
[root@www ~]# mysqldump -u root -p auth > mysql-auth.sql
Enter password:
[root@www ~]# ll mysql-auth.sql
-rw-r--r-- 1 root root 1863 Dec 30 01:01 mysql-auth.sql //备份数据库；
删除数据库auth;
mysql> USE mysql;
Database changed
mysql> DROP DATABASE auth;
Query OK, 1 row affected (0.01 sec)
查看一下是否删除；
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--r-- 1 root root 1775 Dec 30 02:00 /usr/src/mysql-beyond.sql
[root@www ~]# mysql -u root -p beyond < /usr/src/mysql-beyond.sql
Enter password:
[root@www ~]#
备份某个数据库中的某个表；
[root@www ~]# mysqldump -u root -p mysql host user > mysql.host-user.sql
Enter password:
[root@www ~]#
刚装了mysql
sudo apt-get install mysql
安装成功了，安装最后要求输入了密码，也输入了，OK
mysql -uroot -p
输入设置的密码
竟然报错了！
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YSE)
问朋友，他说初始密码是空的，可我命名设置了密码的阿。
密码留空
还是错误！
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
于是重改密码！
#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>
搞定！
(责任编辑：IT)
| 一、创建运行MySQL数据库的系统用户、组。 [root@beyond ~]# useradd -M -s /sbin/nologin mysql 解压并释放源代码： [root@beyond mnt]# tar zxvf mysql-5.0.56.tar.gz -C /usr/src/ 要是配置编译不了，就配置yum源 安装下面的软件； [root@beyond mysql-5.0.56]# yum install *vim* [root@beyond mysql-5.0.56]# yum install -y *termcap* 配置编译； [root@beyond mysql-5.0.56]# ./configure --prefix=/usr/local/apache2 Remember to check the platform specific part of the reference manual for hints about installing MySQL on your platform. Also have a look at the files in the Docs directory. Thank you for choosing MySQL! 编译并安装； [root@beyond mysql-5.0.56]# make && make install 2.建立配置文件； [root@beyond mysql-5.0.56]# cp support-files/my-medium.cnf /etc/my.cnf 初始化数据库； [root@beyond mysql-5.0.56]# /usr/local/mysql/bin/mysql_install_db --user=mysql 修改相关目录的所有权，以便mysql用户可以读写数据库。 [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/ 调整lib库路径： [root@beyond mysql-5.0.56]# echo "/usr/local/mysql/lib/mysql" >> /etc/ld.so.conf 刷新库文件搜索路径，使修改生效 [root@beyond mysql-5.0.56]# ldconfig MySQL启动控制； [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 将mysqld添加为系统服务； [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 设置MySQL程序的执行路径； [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 数据库的基本管理，登录及退出MySQL环境 [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 mysql> 设置root用户的mysql数据库密码； [root@beyond ~]# mysqladmin -u root password "123.com" [root@beyond ~]# mysql -u root -p Enter password: 数据库结构， //查询数据库的命令： mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.00 sec) 查询数据库中的数据表； 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) 数据库的创建和删除； 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) 删除数据库； mysql> DROP DATABASE taotao; Query OK, 0 rows affected (0.00 sec) 创建和删除数据表； 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) 插入新的数据记录； mysql> INSERT INTO auth.users(user_name,user_passwd) VALUES('huangjiajv',ENCRYPT('123456')); 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) 修改数据表信息； mysql> UPDATE auth.users SET user_passwd=ENCRYPT('123.com') WHERE user_name='taotao'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 数据库的备份和恢复； [root@www ~]# mysqldump -u root -p auth > mysql-auth.sql Enter password: [root@www ~]# ll mysql-auth.sql -rw-r--r-- 1 root root 1863 Dec 30 01:01 mysql-auth.sql //备份数据库； 删除数据库auth; mysql> USE mysql; Database changed mysql> DROP DATABASE auth; Query OK, 1 row affected (0.01 sec) 查看一下是否删除； 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--r-- 1 root root 1775 Dec 30 02:00 /usr/src/mysql-beyond.sql [root@www ~]# mysql -u root -p beyond < /usr/src/mysql-beyond.sql Enter password: [root@www ~]# 备份某个数据库中的某个表； [root@www ~]# mysqldump -u root -p mysql host user > mysql.host-user.sql Enter password: [root@www ~]# 刚装了mysql sudo apt-get install mysql 安装成功了，安装最后要求输入了密码，也输入了，OK mysql -uroot -p 输入设置的密码 竟然报错了！ ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YSE) 问朋友，他说初始密码是空的，可我命名设置了密码的阿。 密码留空 还是错误！ ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 于是重改密码！ #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> 搞定！ (责任编辑：IT) |