Mysql备份与恢复+密码重置+远程登陆

设置mysql密码
[root@yiqiang ~]# mysqladmin -uroot password 'dubingyu.com'
就需要密码登陆
[root@yiqiang ~]# mysql -uroot -pdubingyu.com

重置mysql密码
[root@yiqiang ~]# vim /etc/my.conf
在[mysqld]加入一条；不去授权密码登陆
skip-grant

按:wq保存退出，如有误，则检查如下写法，是否有错误   5.1版本以后则skip-grant-tables因为新版本不一样
thread_concurrency = 8
interactive_timeout = 8
wait_timeout = 8
long_query_time = 1
log_slow_queries = /data/mysql/slow.log
skip-grant




[root@yiqiang ~]# vim /etc/my.cnf  编辑配置文件
[root@yiqiang ~]# /etc/init.d/mysqld restart  重启动Mysql
[root@yiqiang ~]# mysql            进入mysql
                mysql> use mysql   使用其中一个库
mysql> update user set password=password('dubingyu.com') where user ='root'; 设置的密码
mysql> select * from user where user='root'\G;     查看表的更改
[root@yiqiang ~]# vim /etc/my.cnf  编辑配置文件
删掉 skip-grant 这个行
[root@yiqiang ~]# /etc/init.d/mysqld restart   重启动
[root@yiqiang ~]# mysql -uroot -pdubingyu.com  用密码登陆


重置mysql密码成功。






3.2 mysql登陆

[root@yiqiang ~]# mysql -uroot -pdubingyu.com  本地登陆
[root@yiqiang ~]# telnet 127.0.0.1 3306  测试端口是否开通
[root@yiqiang ~]# mysql -uroot -h127.0.0.1 -P3306 -pdubingyu.com 
mysql> grant all on *.* to 'root'@'192.168.1.106' identified by '123aaa';  授权
mysql> use mysql
mysql> select * from user where host='192.168.1.106'\G;
[root@yiqiang ~]# mysql -uroot -h192.168.1.106 -P3306 -p123aaa   远程登陆，Ok了
mysql> select user();  查看当前登陆有谁


[root@yiqiang ~]# mysql -uroot -S /tmp/mysql.scok -p  本地登陆，另外一种形式，必须有多个mysql
Enter password:








3.3 mysql常用操作-1
[root@yiqiang ~]# mysql -uroot -pdubingyu.com
mysql> show databases;             查看库
 
mysql> use mysql                   切换库
mysql>  use discuz
mysql> select database();  查看在哪个库下，当前库下
mysql> select user();   查看当前登陆有谁，用户
mysql> select version(); 查看mysql版本号
mysql>quit
     mysql>use discuz      
mysql>show tables;    查看有哪些表
mysql>mysql> desc pre_ucenter_vars;  查看表有哪些行，哪些包含字段
mysql> show create table  pre_ucenter_vars\G;  查看表怎么创建的
mysql> show create table  pre_forum_filter_post\G;

mysql>desc   查看表  一个表例如：
mysql> desc pre_forum_post;


mysql>create database aming; 创建aming库
mysql>use aming;         进入到aming库里面
mysql> create table tb1 (`id` int(4), `name` char(40)) ENGINE=MyISAM DEFAULT CHARSET=gbk;  创建一个表
  mysql>show create table tb1\G;  查看     
 mysql> insert into tb1 values(1, 'aming');  表里插入数据
 mysql> select * from tb1;  查看表
mysql> insert into tb1 values(2, 'linux');  继续插入
 mysql> select * from tb1;  查看表
mysql> insert into tb1 (`id`) values(2); 继续建立第三个
 mysql> select * from tb1;  查看表
mysql> insert into tb1 (`id`) values(4);   继续建立第四个
mysql> insert into tb1 (`name`) values('55');
mysql> insert into tb1 (`name`,`id`) values('55',6); 反过来定义表
mysql> update tb1 set id=5 where name = '55';        更新数据
mysql> delete from tb1 where name='55';  删除 行 带55的
mysql> truncate table aming.tb1          清空 库&表








mysql> drop table tb1;
mysql>drop database aming;
mysql> show databases;    查看库
mysql>








mysql 备份与恢复




[root@yiqiang ~]# mysqldump -uroot -pdubingyu.com discuz   不用备份，可以先查看。
[root@yiqiang ~]# mysqldump -uroot -pdubingyu.com discuz > /data/discuz.sql  备份到/data/discuz.sql
[root@yiqiang ~]# cd /data
[root@yiqiang data]# ls
[root@yiqiang data]# cd /data/mysql/discuz  mysql表存放地方
[root@yiqiang data]#ls 表和库存放地方
[root@yiqiang discuz]# rm -fr pre_forum_post*  不小心把post表删除了
[root@yiqiang discuz]# /etc/init.d/mysqld restart  重启动
页面就显示不到了，帖子块就显示不正常了
[root@yiqiang discuz]# tail /data/mysql/yiqiang.err 错误日志 ，查看 地址 不显示原因


[root@yiqiang discuz]# mysql -uroot -pdubingyu.com discuz < /data/discuz.sql  恢复mysql的
[root@yiqiang discuz]# /etc/init.d/mysqld restart  重启动
页面就显示好了，帖子块就显示正常了




备份
与恢复
# mysqldump -uroot -pdubingyu.com discuz > /data/discuz.sql
# mysql -uroot -pdubingyu.com discuz < /data/discuz.sql
就这俩个命令ok了。




[root@yiqiang discuz]# mysqldump -uroot -pdubingyu.com discuz pre_forum_post >  /data/post.sql 单独备份一个post表
[root@yiqiang discuz]# rm -fr pre_forum_post*  不小心把post表删除了
[root@yiqiang discuz]# /etc/init.d/mysqld restart  重启动
[root@yiqiang discuz]# mysql -uroot -pdubingyu.com discuz < /data/post.sql  恢复mysql的,不需要加表名
[root@yiqiang discuz]# /etc/init.d/mysqld restart






字符集，gbk utf8  latin
没指定字符集恢复时会出现乱码情况。 


[root@yiqiang discuz]# mysqldump -uroot --default-character-set=gbk -pdubingyu.com discuz pre_forum_post >  /data/post.sql   备份


[root@yiqiang discuz]# mysql -uroot --default-character-set=gbk -pdubingyu.com discuz < /data/post.sql  恢复






Mysql 忘记root密码
在/etc/my.cfg [mysqld]下面添加
skip-grant-tables
或是
skip-grant


重启MySQL
use mysql;
UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ;
mysql flush privileges;
将my.cfg文件修改回来 ，重启MySQL即可。


二、在启动Mysql服务器时加上参数--skip-grant-tables来跳过授权表的验证
    (./safe_mysqld --skip-grant-tables &)，这样我们就可以直接登陆Mysql服务器，
    然后再修改root用户的口令，重启Mysql就可以用新口令登陆了。




添加环境变量
PATH=$PATH:/usr/local/mysql/bin  加入到/etc/profile中




只需本机使用Mysql服务
在启动时还可以加上--skip-networking参数使Mysql不监听任何TCP/IP连接
(./safe_mysqld --skip-networking &)，增加安全性。（非常推荐）




查看支持的存储引擎
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance    | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------




查看当前MySQL的默认数据引擎
show variables like '%engine%';
mysql> show variables like '%engine%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| engine_condition_pushdown | ON     |
| storage_engine            | MyISAM |
+---------------------------+--------+
2 rows in set (0.00 sec)




查看和修改最大连接数
  使用MySQL 数据库的站点，当访问连接数过多时，就会出现 "Too many connections" 的错误。
  出现这种错误有两种情况，一种是网站访问量实在太大，服务器已经负担不起，此时就应该考虑负载均衡或者其它减少服务器压力的办法。另一种情况就是 MySQL 的最大连接数设置得太小，当访问量稍大就出现连接过多的错误。


  show variables like '%max_connections%';
  要对 mysql 的最大连接数进行修改，只需要在 my.cnf 配置文件里面修改 max_connections 的值，然后重启 mysql 就行。
  如果 my.ini 文件中没有找到 max_connections 条目，可自行添加以下条目。


  max_connections = 220
  或是
  mysql>set global max_user_connections = 200; [仅本次修改有效]




实时查看mysql当前连接数
前提：对用户和远程主机有授权
mysql> grant all on *.* to root@192.168.9.9 identified by 'ocean@mx';
Query OK, 0 rows affected (0.00 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)




[root@OceanV ~]# mysqladmin -uroot -p -h192.168.9.9  processlist
Enter password:
+----+------+-------------------+----+---------+------+-------+------------------+
| Id | User | Host              | db | Command | Time | State | Info             |
+----+------+-------------------+----+---------+------+-------+------------------+
| 41 | root | localhost         |    | Sleep   | 13   |       |                  |
| 56 | root | 192.168.9.9:45422 |    | Query   | 0    |       | show processlist |
+----+------+-------------------+----+---------+------+-------+------------------+


只查看当前连接数(Threads就是连接数）
[root@OceanV ~]# mysqladmin -uroot -pocean@mx status
Uptime: 4449  Threads: 2  Questions: 18697  Slow queries: 0  Opens: 77  Flush tables: 1  Open tables: 31  Queries per second avg: 4.202
或是 show full processlist;
mysql> show full processlist;
+----+------+-----------+-------+---------+------+-------+-----------------------+
| Id | User | Host      | db    | Command | Time | State | Info                  |
+----+------+-----------+-------+---------+------+-------+-----------------------+
| 41 | root | localhost | NULL  | Query   |    0 | NULL  | show full processlist |
| 65 | root | localhost | cacti | Sleep   |    4 |       | NULL                  |
| 68 | root | localhost | NULL  | Sleep   |   27 |       | NULL                  |
+----+------+-----------+-------+---------+------+-------+-----------------------+
3 rows in set (0.00 sec)
看一下所有连接进程，注意查看进程等待时间以及所处状态 是否locked
如果进程过多，就把进程打印下来，然后查看.
mysql -e 'show full processlist;' -p > 111
查找非locked的进程，一般就是当前执行中卡死，导致后面的进程排队的原因。




查看MySQL连接数和当前用户Mysql连接数
先用管理员身份进入mysql提示符。
mysql -uroot -pxxxx
mysql >show processlist;         #可以显示前100条连接信息
       show full processlist;    #可以显示全部。随便说下，如果用普通账号登录，就只显示这用户的。


修改MySQL监听端口
1.如果你安装的是系统自带的rpm包的情况：
修改 /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
port=3300                    #修改成你自己想要的端口
  socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
保存退出。
2.如果你安装的是其他源码包或者在同一服务器上安装多个mysqld服务的情况：
  同样的配置，找出my.cnf 文件。添加port= 端口号
需要注意的是
[client]
port            = 3300
这里也要改一下。






在同一台机器上配置两个MySQL服务（跑两个端口）
cd /usr/local/; cp -r mysql mysql_2; cd mysql_2;
初始化mysql2: ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql2   
拷贝配置文件：cp /etc/my.cnf ./my.cnf
修改配置文件相关参数：vim my.cnf  #更改port 以及 socket
启动：/usr/local/mysql_2/bin/mysqld_safe --defaults-file=/usr/local/mysql_2/my.cnf --user=mysql &
若开机启动它，需加入到 /etc/rc.local中








查看mysql设置参数　　
$mysqladmin  -u root -p variables;


查询参数　　
    1) \g  在语句尾，同;
　　2) \G  在语句尾，使查询结果以竖形输出
　　3) \c  在语句尾，使当前语句不执行


清屏
\! clear


切换mysql命令结尾符
dlimeter 特殊符;


查看当前版本和当前日期
此命令要求mysql服务器告诉你它的版本号和当前日期


mysql select version(),current_date();
+-------------------------------+----------------+
| version()                     | current_date() |
+-------------------------------+----------------+
| 4.1.7Boco_DC_Group_2004-11-24 | 2008-06-18     |
+-------------------------------+----------------+
1 row in set (0.00 sec)


当前是哪个用户 
select user();


查看mysql状态
show status;


修改mysql参数
show variables like 'max_connect%'; set global max_connect_errors = 1000;
查看mysql队列 show processlist;


查看mysql是否在监听端口命令
netstat -tl | grep mysql




开启服务器　　
    $/etc/rc.d/init.d/mysqld start
　　或
　　$mysqld_safe  --user=mysql &
停止服务器
　　$/etc/rc.d/init.d/mysqld stop
　　或
　　$mysqladmin  shutdown  -u root -p
    或
　　/usr/bin/mysqladmin -u root -p shutdown


　
赋权　　
mysqlgrant all on dbname.* to username@ identified by 'localhostpassword'
刷新权限表　　mysqlflush privileges;


优化表　　
mysqloptimize  table  tblA,tblB;
或 [进行优化并检查修复任务]
$mysqlcheck  -o  dbname  tblA tblB  tblC   -u root  -p


对表进行分析　　
mysqlanalyze  table  tblA;
或
$mysqlcheck  -a dbname [tblA  tblB tblC ] -u root -p


对表进行检查　　
mysqlcheck table tblA,tblB,tblC;


　
连接服务器　　
mysql -u   cnscn
　　      -h   192.168.0.1
　　      [-D  dbname]
　　      [-P  3306]
　　 [--protocol=name]    The protocol of connection (tcp,socket,pipe,memory)
　　      [-S, --socket=name]  连接所用的套接字文件
　　      [--character-sets-dir=name]  字符集所位于的目录           
　　      [--default-character-set=name]   默认的字符集
　　      [-E, --vertical]  垂直地打列出查询输出


从命令行登录MySQL数据库服务器
1.登录使用默认3306端口的MySQL
/usr/local/mysql/bin/mysql -u root -p


2.通过TCP连接管理不同端口的多个MySQL（注意：MySQL4.1以上版本才有此项功能）
/usr/local/mysql/bin/mysql -u root -p --protocol=tcp --host=localhost --port=3307


3.通过socket套接字管理不同端口的多个MySQL
/usr/local/mysql/bin/mysql -u root -p --socket=/tmp/mysql3307.sock


4.通过端口和IP管理不同端口的多个MySQL
/usr/local/mysql/bin/mysql -u root -p -P 3306 -h 127.0.0.1


退出mysql命令：
exit（回车） 提示“bye”


修改登录密码
MySQL默认没有密码，安装完毕增加密码的重要性是不言而喻的。
usr/bin/mysqladmin -u root password 'new-password'
格式：mysqladmin -u用户名 -p旧密码 password 新密码




         
创建数据库　　
Mysql>create database dbname;


显示数据库列表　　
mysql>show databases;


显示数据表列表　　
mysql>show tables;


显示数据表的结构
describe 表名;
或
desc tablesname;


显示表的创建信息
show create table tablesname;




创建名称为ocean的数据库
CREATE DATABASE ocean;
DROP tables tablesname;


将表中记录清空
delete from tablesname;




建表
use 库名；create table 表名(字段设定列表)；




删除名称为ocean的数据库
DROP DATABASE ocean;


选择ocean数据库
USE ocean;


(USE 和 QUIT 命令不需要分号结束。）


常用sql命令
查询 select count(*) from mysql.user; select * from mysql.db; select * from mysql.db where host like '10.0.%';
插入 update db1.t1 set name='aaa' where id=1; 
清空表 truncate table db1.t1;
删除表 drop table db1.t1;
删除数据库 drop database db1;
修复表 repair table tb1 [use frm];






表操作SQL语句
1、显示当前数据库中存在什么表
SHOW TABLES;


2、创建数据库表zhangyan
在mysql后粘贴以下SQL语句，存储引擎为MYISAM，字段id为主键、唯一索引
。
CREATE TABLE `zhangyan` ( `id` INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT , `username` VARCHAR( 20 ) NOT NULL , `password` CHAR( 32 ) NOT NULL , `time` DATETIME NOT NULL , `number` FLOAT( 10 ) NOT NULL , `content` TEXT NOT NULL , PRIMARY KEY ( `id` ) ) ENGINE = MYISAM ;


3、查看zhangyan表结构
DESCRIBE zhangyan;


4、从表中检索信息
4.1、从zhangyan表中检索所有记录
SELECT * FROM zhangyan;


4.2、从zhangyan表中检索特定的行：字段username等于abc，字段number等于1，按字段id降序排列
SELECT * FROM zhangyan WHERE username = abc AND number=1 ORDER BY id DESC;


4.3、从zhangyan表中检索指定的字段：username和password
SELECT username, password FROM zhangyan;


4.4、从zhangyan表中检索出唯一的不重复记录
SELECT DISTINCT username FROM zhangyan;


5、插入信息到zhangyan表
INSERT INTO zhangyan (id, username, password, time, number, content) VALUES (, abc, 123456,
2007-08-06 14:32:12, 23.41, hello world);


6、更新zhangyan表中的指定信息
UPDATE zhangyan SET content = hello china WHERE username = abc;


7、删除zhangyan表中的指定信息
DELETE FROM zhangyan WHERE id = 1;


8、清空zhangyan表
DELETE FROM zhangyan;


9、删除zhangyan表
DROP TABLE zhangyan;


10、更改表结构，将zhangyan表username字段的字段类型改为CHAR(25)
ALTER TABLE zhangyan CHANGE username username CHAR(25);


11、将当前目录下的mysql.sql导入数据库
SOURCE ./mysql.sql;


12、MySQL中将字符串aaa批量替换为bbb的SQL语句
UPDATE表名SET字段名=REPLACE(字段名,aaa,bbb);


13、修复损坏的表
用root帐号从命令行登录MySQL：　　
mysql-uroot-p
选定数据库名（本例中的数据库名为student）：use  student;
修复损坏的表（本例中要修复的表为smis_user_student）：　　repair tables mis_user_student;udent;






一个建库和建表以及插入数据的实例
drop database if exists school; //如果存在SCHOOL则删除
create database school; //建立库SCHOOL
use school; //打开库SCHOOL
create table subject //建立表
(
id int(3) auto_increment not null primary key,
name char(10) not null
); //建表结束
//以下为插入字段
insert into subject(name) values('语文');
insert into subject(name) values('数学');
insert into subject(name) values('英语');


create table student //建立表
(
id int(4) auto_increment not null primary key;
no char(4) not null,
name char(10) not null,
); //建表结束
//以下为插入字段
insert into student(no,name) values('0001','张三');
insert into student(no,name) values('0002','李四');


create table grade //建立表
(
student_no char(4) not null,
subject_id int(3) not null,
score int(3) not null default 0 null,
primary key(student_no,subject_id);
); //建表结束
我们这里建立的grade表student_no参考student的no字段，subject_id参考subject的id字段 它们存在约束关系，
我们给grade表添加两个外键：
subject_id字段：
alter table grade add foreign key(subject_id) refrences subject(id) on delete cascade on update cascade;
student_no字段：
alter table grade add foreign key(student_no) refrences student(no) on delete cascade on update cascade;
这个时候会报错，因为我们没有把student表中的no设为索引，前一句没有问题是因为subject的id是主键，已经是索引了
我们给student的no添加索引
alter table student add index(no);
然后我们再
alter table grade add foreign key(student_no) refrences student(no);
这样就OK了


其他一些操作：
添加主键： alter table 表名 add primary key(列名)
删除主键： alter table 表名 drop primary key;
给某列改名： alter table 表名 change 列名 属性列表 比如alter table student change id no char(4)
添加某列：
1 在表的最前面添加列 比如给student表添加id属性 放在表的最前面
alter table student add id int(4) not null auto_increment primary key first;
2 在表的某个列后加列 比如给student表添加id属性 放在列no的最前面
alter table student add a int(11) afer no;
删除某列： alter table 表名 drop column 列名;
更改某列属性 比如修改student no字段属性 alter table student modify no char(4);
添加索引：alter table 表名 add index 列名
删除索引：alter table 表名 drop index 列名
删除外键：alter table 表名 drop foreign key 约束名
limit的使用：
select * from 表名 limit 2;//如果只给定一个参数，它表示返回最大的记录行数目
select * from 表名 limit 1,3;// 第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目






更改MySQL目录
MySQL默认的数据文件存储目录为/var/lib/mysql。假如要把目录移到/home/data下需要进行下面几步：
1、home
目录下建立data目录
cd /home
mkdir data
2、把MySQL服务进程停掉：
mysqladmin -u root -p shutdown
3、把/var/lib/mysql整个目录移到/home/data
mv /var/lib/mysql　/home/data/
这样就把MySQL的数据文件移动到了/home/data/mysql下
4、找到my.cnf配置文件
如果/etc/目录下没有my.cnf配置文件，请到/usr/share/mysql/下找到*.cnf文件，拷贝其中一个到/etc/并改名为my.cnf)中。命令如下：
[root@test1 mysql]# cp /usr/share/mysql/my-medium.cnf　/etc/my.cnf
5、编辑MySQL的配置文件/etc/my.cnf
为保证MySQL能够正常工作，需要指明mysql.sock文件的产生位置。 修改socket=/var/lib/mysql/mysql.sock一行中等号右边的值为：/home/mysql/mysql.sock 。
操作如下：
　　 vi　 my.cnf
　　 # The MySQL server
　　　 [mysqld]
　　　 port　　　= 3306
　　　#socket　 = /var/lib/mysql/mysql.sock（原内容，为了更稳妥用#注释此行）
　　　 socket　 = /home/data/mysql/mysql.sock　　　（加上此行）
6、修改MySQL启动脚本/etc/rc.d/init.d/mysql
　　最后,需要修改MySQL启动脚本/etc/rc.d/init.d/mysql，把其中datadir=/var/lib/mysql一行中，等号右边的路径改成你现在的实际存放路径：home/data/mysql。
　　[root@test1 etc]# vi　/etc/rc.d/init.d/mysql
　　#datadir=/var/lib/mysql　　　　（注释此行）
　　datadir=/home/data/mysql　　 （加上此行）
7、重新启动MySQL服务
　　/etc/rc.d/init.d/mysql　start

　　如果工作正常移动就成功了，否则对照前面的7步再检查一下。