mysql基础必备操作和备份恢复
mysql> create database backup; ##新建空的数据库Query OK, 1 row affected (0.00 sec)
mysql> quit ##退出数据库
Bye
# cat /etc/my.cnf ##查看mysql的主配置文件，可以看到数据文件目录路径
在/var/lib/mysql下
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# /etc/init.d/mysqld stop ##停止mysql防止数据再写入
停止 mysqld： [确定]
# cd /var/lib/mysql/##cd到数据库数据目录下，
可以看到每一个库都是一个文件夹，每一个表又有多个文件组成。
# ls
backupibdata1ib_logfile0ib_logfile1mysql
# ls mysql/
columns_priv.frmgeneral_log.CSM help_topic.frm proc.frm tables_priv.frm time_zone_transition.frm
columns_priv.MYDgeneral_log.CSV help_topic.MYD proc.MYD tables_priv.MYD time_zone_transition.MYD
columns_priv.MYIgeneral_log.frm help_topic.MYI proc.MYI tables_priv.MYI time_zone_transition.MYI
db.frm help_category.frmhost.frm procs_priv.frmtime_zone.frm time_zone_transition_type.frm
db.MYD help_category.MYDhost.MYD procs_priv.MYDtime_zone_leap_second.frmtime_zone_transition_type.MYD
db.MYI help_category.MYIhost.MYI procs_priv.MYItime_zone_leap_second.MYDtime_zone_transition_type.MYI
event.frm help_keyword.frm ndb_binlog_index.frmservers.frm time_zone_leap_second.MYIuser.frm
event.MYD help_keyword.MYD ndb_binlog_index.MYDservers.MYD time_zone.MYD user.MYD
event.MYI help_keyword.MYI ndb_binlog_index.MYIservers.MYI time_zone.MYI user.MYI
func.frm help_relation.frmplugin.frm slow_log.CSM time_zone_name.frm
func.MYD help_relation.MYDplugin.MYD slow_log.CSV time_zone_name.MYD
func.MYI help_relation.MYIplugin.MYI slow_log.frm time_zone_name.MYI
复制mysql/user.*文件也就是复制数据库mysql.表user的数据复制到backup目录，
就是我刚开始新建的库生成的文件夹。把数据导入到这里。
# cp -rf mysql/user.* backup/
# ls backup/
db.optuser.frmuser.MYDuser.MYI
给backup这个文件夹授权
# chown -R mysql:mysql backup/
# chmod 755 backup
# chmod 660 backup/*
重启服务:
# /etc/init.d/mysqld start
正在启动 mysqld： [确定]
登陆数据库验证：
# mysql -uroot -p123123
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.71 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use backup;##进入backup库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables; ##可以看到新建的库中有我们导入的user表
+------------------+
| Tables_in_backup |
+------------------+
| user |
+------------------+
1 row in set (0.00 sec)
mysql> desc user; ##查看表头
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections| int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.00 sec)
页:
[1]