mysql工作经验

• 以下是根据工作中遇到各种场景用到的一些mysql用法，比较实用，基本是语法之外的一些东西。

　　修改账户密码

　　1.打开mysql控制台，输入原密码；

　　2.输入以下语法：mysql> set password for root@localhost = password('123456')。

 

　　忘了root密码，怎么办？

　　1. 关闭正在运行的mysql服务；

　　2. 打开dos窗口，转到mysql\bin目录；

　　3. 输入mysqld --skip-grant-tables 回车； --skip-grant-tables 的意思是启动mysql服务的时候跳过权限表认证

　　4. 再开一个dos窗口（因为刚才那个dos窗口已经不能动了），转到mysql\bin目录；

　　5. 输入mysql回车，如果成功，将出现mysql提示符 >；

　　7. 连接权限数据库： use mysql；

　　8. 改密码：update user set password=password("123") where user="root";（别忘了最后加分号）；

　　9. 刷新权限（必须步骤）：flush privileges；

　　10. 退出 quit；

　　11. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

 

　　彻底卸载mysql

　　1. 控制面板里卸载mysql；

　　2. 删除c:\program files\mysql文件夹(服务安装位置)；

　　3. 删除c:\documents and settings\all users\application data\mysql文件夹(数据库存贮位置)；

　　4. regedit打开注册表， 删除hkey_local_machine\system\controlset001\services\eventlog\application\mysql；hkey_local_machine\system\controlset002\services\eventlog\application\mysql；hkey_local_machine\system\currentcontrolset\services\eventlog\application\mysql(注册表非必要步骤，实在没办法就删)； 

 

　　一台服务器安装多个mysql服务提供给多个项目作测试环境

　　1. 停止mysql服务；

　　2. 将c:\program files\mysql目录下的所有目录和文件复制到另外一个路径,我这里是copy到e盘；

　　3. 复制出来的mysql文件夹中将my.ini中所有的默认路径改成新的路径，并将所有的port=3306改成3307，好像是有两个地方，具体如下；

    第一个mysql的my.ini文件默认是在如下路径 
    c:\documents and settings\all users\application data\mysql\mysql server 5.5\my.ini 
    复制该ini文件存放到e:\mysql\mysql_base,这个路径可以随意定义,并修改如下内容: 
    [client] 
    port=3307 #第一个数据库的默认端口是3306 这里需要另外启用一个端口 
    # the tcp/ip port the mysql server will listen on 
    port=3307 
    # path to installation directory. 
    basedir="e:\mysql\mysql server 5.5\" #第二个数据库服务安装位置 
    # path to the database root 
    datadir="e:\mysql\mysql server 5.5\data\" #第二个数据库存放位置

　　4. 创建服务：mysqld install mysql2 --defaults-file="e:\mysql\mysql_base\ini\my.ini" 

　　5.修改注册表hkey_local_machine-->system-->currentcontrolset-->services 找到刚才创建的mysql2，

　　   将imagepath修改成如下"e:\mysql\mysql server 5.5\bin\mysqld" --defaults-file="e:\mysql\mysql_base\ini\my.ini"

　　6. 添加环境变量：e:\mysql\mysql server 5.5\bin

　　7. 修改数据库密码，新的数据库默认密码为空，先使用127.0.0.1登入后运行

    1. use mysql；
    2. update user set password=password('root') where user='root'；
    3. flush privileges；
    4. grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option; 

　　8. 添加环境变量：e:\mysql\mysql server 5.5\bin

 

　　删除mysql服务

　　 cmd：sc delete mysql，完了之后重启电脑生效。

 

　　mysql主从备份

　　 1. 先看主机的

　　 1.1 编辑my.ini文件，找到节点[mysqld]，增加三条：

　　　　log-bin=mysql-bin

　　　　server-id=起一个主机服务器id

　　　　binlog-do-db=数据库名字

　　1.2 重启服务

　　1.3.pushd c:\program files\mysql\mysql server 5.5\bin

　　1.4.mysql -h192.168.3.22 -uroot -proot

　　1.5.grant replication slave on *.* to 'mysync'@'%' identified by '123456';

　　1.6.show master status; 通过这一步获取到文件名和日志索引[配置到从机需要]

　　

　　2.再看从机的

　　2.1  编辑my.ini文件，找到节点[mysqld]，增加一条：

　　　　server-id=从机服务器id

　　2.2 重启服务

　　2.3 pushd c:\program files\mysql\mysql server 5.5\bin

　　2.4.mysql -h192.168.3.180 -uroot -proot

　　2.5.change master to master_host='192.168.3.22',master_user='mysync',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=107; 

　　2.6.start slave;

　　2.7.show slave status\g

 

　　3. 确认一致性

　　3.1 对比master_log_file 和 relay_master_log_file 是否一致；

　　3.2 对比read_master_log_pos 和 exec_master_log_pos 是否一致；

　　3.3 slave_io_running: yes；

　　3.4 slave_sql_running: yes；

　　亲测断网断电都可恢复同步；

 

　　账户权限和ip限制

    //进入mysql控制台，删除root远程访问权限，仅允许本机访问
    delete from user where user ='root' and host ='%';

    //保存
    flush privileges;

    //创建mysql只读账户
    grant select on *.*  to readonly@"%" identified by     "sudy@gjwg123456";

    //创建ip限制的账户
    grant all privileges on *.* to 'root'@'192.100.50.73' identified by '        sudy@gjwg123456';

    //保存
    flush privileges;

　　

　　对n张数据结构相同的表批量增加字段

 　　有时候根据模板表让系统自动创建子表用于缓解大数据压力和拆分业务，需要增加字段的时候，就需要对上百张表结构进行改动。

　　 利用concat先批量生成语句，然后全选执行即可。

　　 1. 增加字段：select concat('alter table db_gpsround.',table_name,' add sjlsh int(1) default 0 comment \'审计流水号，0未入库，1已入库\';') as hh from information_schema.tables where table_schema = 'db_gpsround';

　　 2. 增加索引：select concat('alter table db_gps_xlbh.',table_name,' add index zjgxsj(zjgxsj);') as hh from information_schema.tables where table_schema = 'db_gps_xlbh';

 

　　利用mysqldump备份与还原数据库

　　备份整个数据库
　　d:\program files\mysql\mysql server 5.5\bin>mysqldump -uroot -p123 dbparking > e:\mysql_ghost\dbparking.sql
　
　　还原整个数据库
　　d:\program files\mysql\mysql server 5.5\bin>mysql -uroot -p123 testpark < e:\mysql_ghost\dbparking.sql

　　备份表
　　d:\program files\mysql\mysql server 5.5\bin>mysqldump -uroot -p123 dbparking 表1 表2 表3> e:\mysql_ghost\dbparking.sql
　　
　　还原表
　　同上

 

　　定期备份与删除备份

    //利用mysqldump备份，利用winrar压缩，注意备份时会影响其他程序，尽量选择在晚上备份
    @echo on  
    　　cd d:\program files\mysql\mysql server 5.5\bin 
    　　set ymd=%date:~,4%%date:~5,2%%date:~8,2%
    　　set filename=db_dbjs_%ymd%
    　　mysqldump.exe db_dbjs -uroot -psudy1223@2016 -r>     d:\dbserveri\db_back\db_dbjs\%filename%.sql  
    　　c:   
    　　cd c:\program files\winrar
    　　winrar a -m5 -df -ep     d:\dbserveri\db_back\db_dbjs\%filename%.rar      d:\dbserveri\db_back\db_dbjs\%filename%.sql
    @echo off

    //删除3天前的备份文件
    forfiles /p "d:\dbserveri\db_back" /s /m *.* /d -3 /c "cmd /c del @path"