1，针对mysql操作
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass'); 设置密码
update user set password=passworD("test") where user='root';修改密码
flush privileges;
grant all on *.* to root@'%' identified by 'your_password';
mysql> select user,password,host from mysql.user;
+------+-------------------------------------------+-----------+
| user | password | host |
+------+-------------------------------------------+-----------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| root | | 127.0.0.1 |
| root | | ::1 |
| | | localhost |
| repl | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 10.0.5.44 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 10.0.5.44 |
+------+-------------------------------------------+-----------+
当设定密码后，如果要想更改密码如何操作呢？
mysqladmin -u root -p password "
Enter password:
输入原来root的密码就可以更改密码了。
【连接数据库】
刚刚讲过通过使用mysql -u root -p 就可以连接数据库了，但这只是连接的本地的数据库’localhost’，然后有很多时候都是去连接网络中的某一个主机上的mysql。
mysql -u user1 -p –P 3306 -h 10.0.2.69
其中-P（大写）指定远程主机mysql的绑定端口，默认都是3306；-h指定远程主机的IP
3. 查看某个表的字段
mysql> desc func; //func 是表名
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(64) | NO | PRI | | |
| ret | tinyint(1) | NO | | 0 | |
| dl | char(128) | NO | | | |
| type | enum('function','aggregate') | NO | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
4. 查看某个表的表结构（创建表时的详细结构）
mysql> show create table func;
|Table | CreateTable |
| func | CREATE TABLE `func` (
`name` char(64) collate utf8_bin NOT NULL default '',
`ret` tinyint(1) NOT NULL default ',
`dl` char(128) collate utf8_bin NOT NULL default '',
`type` enum('function','aggregate') character set utf8 NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User defined functions' |
+-------+----------------------------------------------------------------------------------------------------------------------
5. 查看当前是哪个用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
6. 查看当前所在数据库
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
7. 创建一个新库
mysql> create database db1;
Query OK, 1 row affected (0.04 sec)
8. 创建一个表
mysql> create table t1 ( `id` int(4), `name` char(40));
Query OK, 0 rows affected (0.02 sec)
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
9. 查看当前数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.86 |
+-----------+
10. 查看当前系统时间
mysql> select current_date, current_time;
+--------------+--------------+
| current_date | current_time |
+--------------+--------------+
| 2011-05-31 | 08:52:50 |
+--------------+--------------+
11. 查看当前mysql的状态
mysql> show status;
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 1 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 664 |
| Bytes_sent | 6703 |
这个命令打出很多东西，显示你的mysql状态。
12. 查看mysql的参数
mysql> show variables;
很多参数都是可以在/etc/my.cnf中定义的。
13. 创建一个普通用户并授权
mysql> grant all on *.* to user1 identified by ';
Query OK, 0 rows affected (0.01 sec)
all 表示所有的权限（读、写、查询、删除等等操作），*.*前面的*表示所有的数据库，后面的*表示所有的表，identified by 后面跟密码，用单引号括起来。这里的user1指的是localhost上的user1，如果是给网络上的其他机器上的某个用户授权则这样：
mysql> grant all on db1.* to ';
Query OK, 0 rows affected (0.00 sec)
用户和主机的IP之间有一个@，另外主机IP那里可以用%替代，表示所有主机。例如：
mysql> grant all on db1.* to ';
Query OK, 0 rows affected (0.00 sec)
【一些常用的sql】
1. 查询语句
mysql> select count(*) from mysql.user;
mysql.user表示mysql库的user表；count(*)表示表*有多少行。
mysql> select * from mysql.db;
查询mysql库的db表中的所有数据
mysql> select db from mysql.db;
查询mysql库db表的db段。
mysql> select * from mysql.db where host like '10.0.%';
查询mysql库db表host字段like 10.0.% 的行，这里的%表示匹配所有，类似于前面介绍的通配符。
2. 插入一行
mysql> insert into db1.t1 values (1, 'abc');
Query OK, 1 row affected (0.00 sec)
t1表在前面已经创建过。
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
3. 更改某一行
mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
这样就把原来id为1的那行中的name改成’aaa’
4. 删除表
mysql> drop table db1.t1;
Query OK, 0 rows affected (0.01 sec)
5. 删除数据库
mysql> drop database db1;
Query OK, 0 rows affected (0.07 sec)
6. 备份与恢复库
mysqldump -uroot -p mysql >mysql.sql
这里的mysqldump 就是备份的工具了，-p后面的mysql指的是mysql库,把备份的文件重定向到mysql.sql。如果恢复的话，只要：
mysql -uroot -p mysql < mysql.sql
2.python链接数据库并执行备份
import pymysql
import os, sys, tarfile, datetime, re, time, subprocess
Date = time.strftime('%Y%m%d_%H-%M')
print(Date)
flage = None
Flage = None
class mysql_backup(object):
def __init__(self,res,host,user,pwd,gamedb,path):
self.res = res
self.host = host
self.user = user
self.pwd = pwd
self.gamedb = gamedb
self.path = path
def connect(self):
f = open(self.path + "errlog_db.log","a+",encoding="utf-8")
try:
conn = pymysql.connect(host=self.host, user=self.user, passwd=self.pwd, db=self.gamedb)
print("connect ok")
global flage
flage = True
except Exception as e:
f.write(Date + ':' + 'connect to mysqldb error!!!' + '\n')
sys.exit()
conn.close()
f.close()
def sqlfile(self):
global flage, Flage
if flage:
f = open(self.path + "errlog_db.log", "a+", encoding="utf-8")
try:
os.system('%s -u%s -p%s -h%s %s >> %sgamedb.sql' % (self.res,self.user,self.pwd,self.host,self.gamedb,self.path))
tarsql = tarfile.open( self.path + Date + '.tar', 'w')
tarsql.add(self.path + 'gamedb.sql',arcname= Date + ".gamedb.sql")
tarsql.close()
Flage = True
print("sqlfile ok %s" % self.user)
except Exception as e:
f.write(Date + ':' + 'sqlfile to tar error!!!' + '\n')
sys.exit()
f.close()
def refile(self):
global flage, Flage
if flage and Flage:
if os.path.isfile(self.path + 'gamedb.sql'):
os.system("rm -f" + " " + self.path + "gamedb.sql")
print("refiel ok")
def errlog(self):
if os.path.isdir(self.path) is False:
os.makedirs(self.path)
print("path ok")
if os.path.isfile(self.path + 'errlog_db.log') is False:
os.system("touch" + " " + self.path + "errlog_db.log")
print("errlog ok")
def oldfile(self):
settime= (datetime.datetime.now() - datetime.timedelta(days=1)).strftime('%Y%m%d') #获取到30天前的时间
settime = int(settime)
for dirpath,dirnames,filenames in os.walk(self.path):
for file in filenames:
if file != 'errlog_db.log':
filetime = time.strftime('%Y%m%d', time.localtime(os.stat(self.path + file).st_ctime)) #获取文件时间
filetime = int(filetime)
print(file)
if settime == filetime:
os.remove(self.path + file)
#需要你手动更改打开的文件位置，注意#行需要更改
def config():
file = open("start", "r", encoding="utf-8") #打开文件位置
host = re.findall(r'-db_host="(\w.*?)"', file.read())
str_host = ''.join(host)
file.close()
file = open("start","r",encoding="utf-8")
dbname = re.findall(r'-db_name="(\w.*?)"',file.read()) #打开文件位置
str_dbname = ''.join(dbname)
file.close()
res = subprocess.Popen("which mysqldump", shell=True, stdout=subprocess.PIPE)
res = res.stdout.read().decode().strip()
parameter = mysql_backup(res,str_host,',str_dbname,r'/data/backup_db/') #用户，密码需要更改，路径
parameter.errlog()
parameter.connect()
parameter.sqlfile()
parameter.refile()
parameter.oldfile()
if __name__ == "__main__":
config()
3.redis操作。。。。