mysql一些常见命令

ゝ一世哀愁。 2022-03-22 13:10 324阅读 0赞

**根据日常使用情况总结了mysql的一些常见命令**

//在root用户中，启动mysql服务  
service mysqld start

//查看启动状态  
service mysqld status

//停止mysql服务  
service mysqld stop

//切换到mysql用户  
//首先查看mysql的安装位置,切换到安装位置  
whereis  mysql  
//进入到mysql客户端  
./bin/mysql -u root -p

//查看引擎  
mysql> show  engines;

//设置root用户密码  
 set password= password('mysql');  
下一次进入mysql客户端需要输入设置的此密码。  
   
//使用安全模式启动mysql 服务，这个操作执行后控制台会停住，需要用户在另外一个shell 窗口中继续操作  
bin/mysqld\_safe --user=mysql --skip-grant-tables --skip-networking --basedir=/opt/mysql-5.5.58-linux-glibc2.12-x86\_64 --datadir=/opt/mysql-5.5.58-linux-glibc2.12-x86\_64/data

//kill 掉安全模式的mysql服务  
kill -15 $(ps -ef |grep "mysqld --basedir" | grep -v grep | awk -F ' ' '\{print $2\}')  
   
   
//创建用户  
1、创建用户（其中用户名为havi2 、 用户密码为123456 、 %表示所有用户可以连接）  
mysql>create user 'havi2'@'%' identified by '123456';    // 此方法创建后可通过mysql.user查看 select user,host from mysql.user;  
mysql>grant all privileges on test.\* to havi2@'%' identified by '123456';  //给havi2用户赋值test库的所有权限,也可以只赋值select, insert, update, delete

2、增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MYSQL，然后键入以下命令：  
mysql>grant select,insert,update,delete on \*.\* to test1@"%" Identified by "abc";  //此方法创建 ，不能使用命令行方式连接

3、创建用户test1 让其所有用户可登陆   //此方法创建最建议  
mysql>grant all privileges on \*.\* to 'test1'@'%' identified by 'abcd' with grant option;  
mysql>flush   privileges;

//删除用户(删除host为localhost、user为 test)  
mysql>delete from mysql.user where user='test' and host='localhost';

//删除账户及权限：  
>drop user 用户名@'%';  
>drop user 用户名@ localhost;

//删除匿名用户  
select user , host from mysql.user; ← 查看用户信息  
select password , user from mysql.user;   查看用户和密码  
delete from mysql.user where user=''; ← 删除匿名用户

//创建表  
create table test2(name VARCHAR(20) , age int );

//赋予任何主机访问数据库权限（方法一）  
mysql> update mysql.user set host='%' where user='root' and host='localhost';  
mysql>  select host ,user from user;  
\+-----------+------+  
| host      | user |  
\+-----------+------+  
| %         | root |  
| 127.0.0.1 | root |  
| ::1       | root |  
| localhost |      |  
| sdb3      |      |  
| sdb3      | root |  
\+-----------+------+

//赋予任何主机访问数据的权限 （方法二）  
1.本地操作的权限.为避免冲突可将root用户改为自定义用户  
mysql> GRANT ALL PRIVILEGES ON \*.\* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;  
2.远程操作的权限  
mysql> GRANT ALL PRIVILEGES ON \*.\* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;  
3.远程操作的权限不设置密码  
mysql> GRANT   ALL   PRIVILEGES   ON   \*.\*   TO   'root'@'%'   WITH   GRANT   OPTION;  
//接下来一定要执行这一步，不然配置不生效  
mysql> FLUSH   PRIVILEGES;  
mysql> exit  
//重新启动进入数据库查看  
mysql>  select host ,user from mysql.user;  
\+-----------+------+  
| host      | user |  
\+-----------+------+  
| %         | root |  
| 127.0.0.1 | root |  
| ::1       | root |  
| localhost |      |  
| localhost | root |  
| sdb3      |      |  
| sdb3      | root |  
\+-----------+------+

//恢复原来的配置  
mysql> update mysql.user set host='localhost' where user='root' and host='%';

//导出数据sql  
SELECT \* from test.test into outfile '/opt/mysql-5.5.58-linux-glibc2.12-x86\_64/test.csv'   fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\\r\\n';

//使用mysqldump工具进行导出  
mysqldump -uroot -ppassword cardstore table1 table2 --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\\r\\n' --tab=/mysqldump\_dir

//将test这个database导出到test.sql中  
./bin/mysqldump -uroot -pmysql --databases test >test.sql

SELECT  
    \* from test  
into outfile '/opt/mysql-5.5.58-linux-glibc2.12-x86\_64/test.csv'     
fields terminated by ',' optionally enclosed by '"' escaped by '"'      
lines terminated by '\\r\\n';

SELECT \* from test.test into outfile '/opt/mysql-5.5.58-linux-glibc2.12-x86\_64/test/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\\r\\n';

//注意执行导出时会报错  
1.执行导出报错异常为：  
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement  
2.进入mysql客户端查看其中secure-file-priv参数为null，表示禁止导出数据。  
mysql>  show global variables like '%secure%';    
\+------------------+-------+  
| Variable\_name    | Value |  
\+------------------+-------+  
| secure\_auth      | OFF   |  
| secure\_file\_priv | NULL  |  
\+------------------+-------+

3.修改/etc/my.cnf    
在\[mysqld\]中增加 secure\_file\_priv="/"

4.重启mysqld服务  
在root用户下执行：  
    service mysqld stop      
    service mysqld start  
    service mysqld status  
      
5、查看 show global variables like '%secure%'; 发现其参数配置已经改变  
mysql> show global variables like '%secure%';  
\+------------------+-------+  
| Variable\_name    | Value |  
\+------------------+-------+  
| secure\_auth      | OFF   |  
| secure\_file\_priv | /     |  
\+------------------+-------+

mysql 用户  
\-u root   -p mysql  
\-u test1  -p abc

========================================执行步骤  
1、创建一个新的用户，让所有用户可连接  
mysql> GRANT ALL PRIVILEGES ON \*.\* TO 'test1'@'%' IDENTIFIED BY 'abc' WITH GRANT OPTION;  
mysql> FLUSH   PRIVILEGES;

2、执行Export脚本使数据导出来  
sh mysql\_export.sh -h localhost -P 3306 -u test1 -p abc -d test -t test -f /opt/tools/mysql\_to\_sdb/

3、执行脚本，导出表结构  
java -jar mysql.jar 192.168.137.131 3306 havi password test

4、执行 mysql\_to\_sdb 脚本  
sh mysql\_to\_sdb.sh -i 192.168.137.131 -p 3306 -u test1 -w abc -d test  -f /opt/tools/mysql\_to\_sdb

\-i为地址IP  
\-p为mysql的端口号  
\-u为mysql用户名  
\-w为用户名所对用的密码  
\-d为需要执行的database  
\-f为执行路劲

\---mysql使用事务  
mysql使用事务的关键字

1.begin  //打开一个事务  
2.commit //提交到数据库  
3.rollback //取消操作  
4.savepoint //保存，部分取消，部分提交

\---mysql创集索引  
1.添加PRIMARY KEY（主键索引）  
mysql>ALTER TABLE \`table\_name\` ADD PRIMARY KEY ( \`column\` )  
2.添加UNIQUE(唯一索引)  
mysql>ALTER TABLE \`table\_name\` ADD UNIQUE ( \`column\` )  
3.添加INDEX(普通索引)  
mysql>ALTER TABLE \`table\_name\` ADD INDEX index\_name ( \`column\` )  
4.添加FULLTEXT(全文索引)  
mysql>ALTER TABLE \`table\_name\` ADD FULLTEXT ( \`column\`)  
5.添加多列索引  
mysql>ALTER TABLE \`table\_name\` ADD INDEX index\_name ( \`column1\`, \`column2\`, \`column3\` )

\---查看 mysql的 server id  
  show variables like 'server\_id';

\----查看mysql的 binlog 模式  
 show variables like 'binlog\_format';    
   
   
 ------MySQL的binlog三种模式  
 1、statement level模式

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。  
优点：statement level下的优点，首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约io，提高性能。因为他只需要记录在master上所执行的语句的细节，以及执行语句时候的上下文的信息。  
缺点：由于它是记录的执行语句，所以为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是,由于mysql现在发展比较快，很多的新功能加入，使mysql的复制遇到了不小的挑战,自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement level下，目前已经发现的就有不少情况会造成mysql的复制问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如sleep()在有些版本就不能正确复制。

2、rowlevel模式

日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改  
优点：bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以row level的日志的内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function,以及trigger的调用和触发无法被正确复制的问题。  
缺点：row level下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改记录，这样可能会产生大量的日志内容，比如有这样一条update语句：update product set owner\_member\_id='d' where owner\_member\_id='a',执行之后，日志中记录的不是这条update语句所对应的事件(mysql是以事件的形式来记录bin-log日志)，而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的很多事件。自然，bin-log日志的量会很大。

3、mixed模式  
实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选一种。新版本中的statement level还是和以前一样，仅仅记录执行的语句。而新版本的mysql中对row level模式被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete 等修改数据的语句，那么还是会记录所有行的变更。