MySQL数据库安装及配置、异常解决.doc

目 录一、MySQL数据库删除2二、MySQL数据库安装2三、远程连接MYSQL提示Host is not allowed to connect to this MySQL server2四、Access denied for user localhost to database mysql4五、linux的mysql的大小写配置5六、创建或导入函数时，MySQL创建Function时报的1418错误代号解决办法5七、查询数据库的字符集合6八、mysql noinstall 安装6九、linux centos 5.3 mysql数据库同步 定时备份 及恢复6十、MySQL数据库导入导出详解转发7一、 MySQL数据库删除1.rpm -qa mysql2.yum remove mysql 删之前先备份： 1. roottt1 #cd /var/lib/mysql ; cp -r * /backup/mysql/备份数据库 2. roottt1 # mysqldump -uroot -p yourdatabase|gzip -9 yourdatabase.date.gz 3. roottt1 # rm /etc/f4 . roottt1 # rm -r /var/lib/mysql参考网址:/?uid-21943618-action-viewspace-itemid-41185二、 MySQL数据库安装1.用到的包: MySQL-server-5.5.15-1.linux2.6.i386.rpm MySQL-client-5.5.15-1.linux2.6.i386.rpm 2. roottt1 # rpm -ivh MySQL-*3. roottt1 # cd /usr/share/mysql ; roottt1 # cp f /etc/f ;4. /etc/rc.d/init.d/mysql restart参考网址:/?uid-21943618-action-viewspace-itemid-41185三、 远程连接MYSQL提示Host is not allowed to connect to this MySQL server参考网址：/share/detail/31572332如果你想连接你的mysql的时候发生这个错误：ERROR 1130: Host is not allowed to connect to this MySQL server1。 改表法。可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 mysql 数据库里的 user 表里的 host 项，从localhost改称%mysql -u root -pvmwaremysqluse mysql;mysqlupdate user set host = % where user = root;mysqlselect host, user from user;2. 授权法。例如，你想myuser使用mypassword从任何主机连接到mysql服务器的话。GRANT ALL PRIVILEGES ON *.* TO myuser% IDENTIFIED BY mypassword WITH GRANT OPTION;如果你想允许用户myuser从ip为的主机连接到mysql服务器，并使用mypassword作为密码GRANT ALL PRIVILEGES ON *.* TO root IDENTIFIED BY mypassword WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO root IDENTIFIED BY 1235 WITH GRANT OPTION;mysqlflush privileges; 这句一定要加上！四、 Access denied for user localhost to database mysql参考网址：/blog/static/148025420103710128776/最近新装好的mysql在进入mysql工具时，总是有错误提示:# mysql u root -pEnter password:ERROR 1045 (28000): Access denied for user rootlocalhost (using password: NO)使用网上介绍的方法修改root用户的密码：# mysqladmin -uroot -p password newpasswordEnter password:mysqladmin: connect to server at localhost failederror: Access denied for user rootlocalhost (using password: YES)解决方法，如下（请先测试方法三）：方法一：# /etc/init.d/mysql stop# mysqld_safe -user=mysql -skip-grant-tables -skip-networking &# mysql -u root mysqlmysql UPDATE user SET Password=PASSWORD(newpassword) where USER=root;mysql FLUSH PRIVILEGES;mysql quit# /etc/init.d/mysql restart# mysql -uroot -pEnter password: mysql方法二：直接使用/etc/mysql/f文件中client节提供的用户名和密码:# mysql -udebian-sys-maint -pEnter password: mysql UPDATE user SET Password=PASSWORD(newpassword) where USER=root;mysql FLUSH PRIVILEGES;mysql quit# mysql -uroot -pEnter password: mysql方法三：# mysql -uroot -pEnter password: 至此，困惑多时的问题解决了！五、 linux的mysql的大小写配置参考网址：/blog/1171325忽略大小写的配置：在配置文 件的 /etc/mysql 下的f 中 mysqld区段下增加一行 lower_case_table_names=1 。 六、 创建或导入函数时，MySQL创建Function时报的1418错误代号解决办法参考网址：/blog/1171325以下是引用片段：ErrorCode:1418ThisfunctionhasnoneofDETERMINISTIC,NOSQL,orREADSSQLDATAinitsdeclarationandbinaryloggingisenabled(you*might*wanttousethelesssafelog_bin_trust_function_creatorsvariable)(0mstaken)解决方法如下：1. mysql SET GLOBAL log_bin_trust_function_creators = 1;2. 系统启动时 -log-bin-trust-function-creators=13. 在my.ini(linux下为my.conf)文件中 mysqld 标记后加一行内容为 log-bin-trust-function-creators = 1-如果在create function的时候有 1418的错语的时候:那么只需要执行set global log_bin_trust_routine_creators=1;然后怎么create function 都不会有问题(当然,你的function必段正确哦),这是mysql的一个bug,搞不懂为什么,反正这样做就OK了.另外，也可以直接在配置文件f中添加如下行mysqld log_bin_trust_routine_creators=1;这个命令可以在sql editor的环境中运行，并不需要重新启动服务哦。七、 查询数据库的字符集合-1、数据库编码select distinct table_schema,table_collation from information_schema.TABLESwhere table_schema not in(performance_schema,sys,test,mysql,information_schema);-2、表编码select distinct table_schema,table_collation, table_name from information_schema.TABLESwhere table_schema not in(performance_schema,sys,test,mysql,information_schema) ;- 3、修改表的字符集alter table cam.account charset utf8;- 4、建库delimiter $CREATE DATABASE csf_fdp /*!40100 DEFAULT CHARACTER SET utf8 */$八、 mysql noinstall 安装参考网址：/lufei_19840816/blog/static/616172732010223112615548/?fromdm&fromSearch&isFromSearchEn