mysql>use mysql
Database changed
2:查看数据库的命令
mysql>show databases
3:查看数据库中表的命令
mysql>show tables
4:查看表的详细结构
mysql>desc tablename
5:新建数据库
mysql>create database school
Query OK, 1 row affected (0.00 sec)
6:新建表
mysql>create table user01(
->id varchar(20) NOT NULL,
->userName varchar(10) NOT NULL,
->age int(11) default'0',
->sex char(2) NOT NULL default'm',
->PRIMARY KEY (id)
->)TYPE=InnoDB
Query OK, 0 rows affected, 1 warning (0.02 sec)mysql>desc student
7:插入
mysql>insert into student(id,stuName) values('1','tomcat')
Query OK, 1 row affected (0.00 sec)
8:删除
mysql>delete from student where id='1'
Query OK, 1 row affected (0.01 sec)
9:删除表中所有数据
mysql>truncate table student
Query OK, 1 row affected (0.01 sec)
10:创建新用户并给予权限
mysql>grant all privileges on *.* to dbsync@"127.0.0.1" identified by "1234"
11:更改Mysql用户密码
c:\Mysql5.0\bin>mysqladmin -u root -p password 1234
Enter password: ****
备份数据库及表
我们用mysqldump命令来备份数据库
c:\mysql\bin\>mysqldump –u root –p 3306 mysql>d:\backup.sql
执行此语句将把mydb 备份到D盘的backup.sql文件中
备份多个数据库表
linux系统下使用脚本定时备份数据库,代码如下:#!/bin/bash
#Setting
#设置数据库名,数据库登录名,密码,备份路径,日志路径,数据文件位置,以及备份方式
#默认情况下备份方式是mysqldump,还可以是mysqldump,mysqldotcopy,如果注释掉第
#18行,则默认以tag的方式备份
#默认情况下,用root(空)登录mysql数据库,备份至/root/dbxxxxx.tgz
DBName=mysql
DBUser=root
DBPasswd=123456
BackupPath=/home/wang/www/backup/mysql/mysqlbackup
LogFile=/home/wang/www/backup/mysql/db.log
DBPath=/var/lib/mysql/
#BackupMethod=mysqldump
#BackupMethod=mysqlhotcopy
#BackupMethod=tar
#Setting End
NewFile="$BackupPath"db$(date +%y%m%d).tgz
DumpFile="$BackupPath"db$(date +%y%m%d)
OldFile="$BackupPath"db$(date +%y%m%d --date='5 days ago').tgz
echo "-------------------------------------------" >>$LogFile
echo $(date +"%y-%m-%d %H:%M:%S") >>$LogFile
echo "--------------------------" >>$LogFile
#Delete Old File
if [ -f $OldFile ]
then
rm -f $OldFile >>$LogFile 2>&1
echo "[$OldFile]Delete Old File Success!" >>$LogFile
else
echo "[$OldFile]No Old Backup File!" >>$LogFile
fi
if [ -f $NewFile ]
then
echo "[$NewFile]The Backup File is exists,Can’t Backup!" >>$LogFile
else
case $BackupMethod in
mysqldump*)
if [ -z $DBPasswd ]
then
mysqldump -u $DBUser --opt $DBName >$DumpFile
else
mysqldump -u $DBUser -p$DBPasswd --opt $DBName >$DumpFile
fi
tar czvf $NewFile $DumpFile >>$LogFile 2>&1
echo "[$NewFile]Backup Success!" >>$LogFile
rm -rf $DumpFile
mysqlhotcopy*)
rm -rf $DumpFile
mkdir $DumpFile
if [ -z $DBPasswd ]
then
mysqlhotcopy -u $DBUser $DBName $DumpFile >>$LogFile 2>&1
else
mysqlhotcopy -u $DBUser -p $DBPasswd $DBName $DumpFile >>$LogFile 2>&1
fi
tar czvf $NewFile $DumpFile >>$LogFile 2>&1
echo "[$NewFile]Backup Success!" >>$LogFile
rm -rf $DumpFile
*)
/etc/init.d/mysqld stop >/dev/null 2>&1
tar czvf $NewFile $DBPath >>$LogFile 2>&1
/etc/init.d/mysqld start >/dev/null 2>&1
echo "[$NewFile]Backup Success!" >>$LogFile
esac
fi
echo "-------------------------------------------"
echo
#lftp -f lftp.sh 如果有FTP可同时上传ftp中。
再利用crontab命令生成/var/spool/cron/root 文件:
#crontab –e
//打开一个编辑窗口,第一行会有内容格式的提示,在此输入命令
//m h dom mon dow command
//具体意义表示:分钟 小时 日期 月份 星期 命令,在某月(mon)的某天(dom)或者星期几(dow)的几点(h,24小时制)几分(m)执行某个命令(command)
//如: 10 02 * * * /home/backup/autobackupmysql.sh (表示零晨二点十分执行备份脚本)
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)