Linux下Mysql的一些操作

    <div id="blog_text" class="cnt"><span style="text-decoration: underline;">1、linux下启动mysql的命令:</span><br>/etc/init.d/mysql start<br><span style="text-decoration: underline;">2、关闭mysql的命令:</span><br>/etc/init.d/mysql shutdown<br><span style="text-decoration: underline;">3、重启mysql的命令</span>：<br>/etc/init.d/mysql restart<br><span style="text-decoration: underline;">4、创建用户</span>：<br>grant 权限(all代表所有权限) on database_name.* to usernmae@"%" identified by "password"<br>grant privileges (columns) on what to user identified by "password" with grant option<br>如果创建用户后或者update mysql-user表后权限的设置不生效,可以执行flush privileges;就ok!
mysql>grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by '123';<br>给来自10.163.225.87的用户joe分配可对数据库vtdc的employee表进行select,insert,update,delete,create,drop等操作的权限，并设定口令为123。
mysql>grant all privileges on vtdc.* to joe@10.163.225.87 identified by '123';<br>给来自10.163.225.87的用户joe分配可对数据库vtdc所有表进行所有操作的权限，并设定口令为123。
mysql>grant all privileges on *.* to joe@10.163.225.87 identified by '123';<br>给来自10.163.225.87的用户joe分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。
mysql>grant all privileges on *.* to joe@localhost identified by '123';<br>给本机用户joe分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。 <br><br><span style="text-decoration: underline;">5、导入数据</span>:<br>在mysql的命令行 source /root/daoru.sql;<br><br><span style="text-decoration: underline;">6、linux下解决mysql数据库大小写的问题</span>:<br>修改/etc/my.cnf文件,在[mysqld]下加一行:lower_case_table_names=1 (修改完后需要重启动mysql才能生效)<br>如果/etc下没有my.cnf文件，就把/usr/share/mysql目录下的my-medium.cnf拷贝到/etc目录下,重命名为my.cnf,然后再修改,最后重启mysql就ok了.可以在mysql的命令行检查当前的设置是否区分大小写:<br><br>mysql> show variables like '%case_table%';<br><br>+------------------------+-------+<br><br>| variable_name           | value |<br><br>+------------------------+-------+<br><br>| lower_case_table_names | 1      |<br><br>+------------------------+-------+<br>(0：区分；1：不区分)<br><br><span style="text-decoration: underline;">7、自动启动</span><br>1）察看mysql是否在自动启动列表中<br>[root@test1 local]# /sbin/chkconfig –list<br>2）把mysql添加到你系统的启动服务组里面去<br>[root@test1 local]# /sbin/chkconfig – add mysql<br>3）把mysql从启动服务组里面删除。<br>[root@test1 local]# /sbin/chkconfig – del mysql<br><br><span style="text-decoration: underline;">8、修改mysql字符编码</span>:<br><br>打开配置文件/etc/my.cnf(修改完后需要重启动mysql才能生效)<br>在[client] 添加    default-character-set=utf8 默认字符集为utf8<br>在[mysqld] 添加    default-character-set=utf8 默认字符集为utf8<br>init_connect='set names utf8' (设定连接mysql数据库时使用utf8编码，以让mysql数据库为utf8运行)<br><br>mysql> show variables like 'character_set%';<br>+--------------------------+----------------------------+<br>| variable_name | value |<br>+--------------------------+----------------------------+<br>| character_set_client | utf8 |<br>| character_set_connection | utf8 |<br>| character_set_database | utf8 |<br>| character_set_filesystem | binary |<br>| character_set_results | utf8 |<br>| character_set_server | utf8 |<br>| character_set_system | utf8 |<br>| character_sets_dir | /usr/share/mysql/charsets/ |<br>+--------------------------+----------------------------+<br><br><span style="text-decoration: underline;">9、查看mysql数据库里已经创建好的存储过程和存储函数</span>：<br><br>show procedure status<br>show function status<br>查看某个存储过程的代码：show create procedure status<br><br>对于存储过程,如果是root用户创建的,其他用户则无法访问,可以用如下的方式来解决:<br>mysql> show create procedure `database`.`procedure_name`\g<br>mysql> grant select on mysql.proc to user@'host' identified by 'passwd';<br>注意:identified by 'passwd' 的用途是修改密码,如果不想修改密码的话就不要加这句话.<br><br><span style="text-decoration: underline;">10、查看表结构</span>：desc table_name;<br>linux下查看到3306端口的tcp链接：netstat -an | grep 3306<br>mysql命令行下查看连接池的链接：show processlist<br>linux下远程链接mysql数据库：mysql -h192.168.1.1 -u -p<br><br><span style="text-decoration: underline;">11、mysql的用户权限</span><br><br>首先是用户的创建，可以用grant<br>grant all privileges on *.* to 'monty'@'localhost' identified by 'some_pass' with grant option;<br><br>也可以直接插入：<br>insert into user (host,user,password) values('localhost','dummy',password('some_pass'));<br>flush privileges;