标签归档:mysql

恢复mysql数据库的几种方法

1.如果数据量超大,直接拷贝数据库文件,以拷贝之前要先停止mysqld服务,如果是myisam类型则可以可以直接恢复。如果是innodb,还需要copy ibdata1.

2.如果数据量中等大小,是用命令行工具,mysqldump,注意编码。

3.如果中小型 数据量,用第三方工具,如sqlyog,phpmyadmin可能更省事。

centos5.4安装配置mysql5.5.8+sphinx0.9.9

#ready:
#download mysql from : http://www.mysql.com/downloads/mirror.php?id=401062#mirrors
$ wget http://downloads.mysql.com/archives/mysql-5.5/mysql-5.5.8.tar.gz
$ svn co http://sphinxsearch.googlecode.com/svn/trunk sphinx-trunk
$ wget http://www.coreseek.cn/uploads/csft/patch/Sphinx_1.11-dev_sphinxse_mysql-5.5.8.diff
$ wget http://www.coreseek.cn/uploads/csft/patch/Sphinx_1.11-r2652-dev_sphinxse_mysql-5.5.8.diff
$ tar xzvf mysql-5.5.8.tar.gz

#patch:
$ cd sphinx-trunk
$ patch -p0 < ../Sphinx_1.11-dev_sphinxse_mysql-5.5.8.diff
$ cp -R mysqlse ../mysql-5.5.8/storage/sphinx
$ rm mysqlse/CMakeLists.txt mysqlse/ha_sphinx.cc mysqlse/ha_sphinx.h
$ svn up -r 2652
$ patch -p0 < ../Sphinx_1.11-r2652-dev_sphinxse_mysql-5.5.8.diff
$ cp -R mysqlse ../mysql-5.5.8/storage/sphinx

#build:
$ cd ../mysql-5.5.8
$ cmake . -DCMAKE_BUILD_TYPE=Release -DWITH_SPHINX_STORAGE_ENGINE=1 #可能会提示CMAKE_BUILD_TYPE有问题,去掉略过
$ make
$ make install
$ ls -l storage/sphinx/ha_sphinx.*
#you'll see ha_sphinx.so.
$ cp support-files/mysql.server /etc/init.d/mysqld
$ chmod 755 /etc/init.d/mysqld
$ bash scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/app/mysql/data/
$ /etc/init.d/mysqld start
如果出现: /etc/init.d/mysqld: line 256: my_print_defaults: command not found
Starting MySQLCouldn't find MySQL server (./bin/mysqld_safe[FAILED]
则修改 /etc/init.d/mysqld中或/etc/my.cnf中的basedir和datadir
$ ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
#tip:
#now ,you can run "make install" to install mysql at /usr/local/mysql.
#or , you can copy ha_sphinx.so to the patch lib/plugin/ in your mysql install directory.

#install plugin:
#login mysql with user root
$ mysql
mysql>INSTALL PLUGIN sphinx SONAME "ha_sphinx.so"
mysql>SHOW ENGINES;
#you'll see SPHINX in the ENGINE list.

centos5.5(64bit)下用rpm包安装mysql5.5.8[转]

一 安装文件
MySQL-server-5.5.8-1.rhel5.x86_64.rpm
MySQL-client-5.5.8-1.rhel5.x86_64.rpm
执行安装命令
rpm -ivh MySQL-server-5.5.8-1.rhel5.x86_64.rpm
rpm -ivh MySQL-client-5.5.8-1.rhel5.x86_64.rpm
安装完以后,就可以启动了
service mysql start
service mysql stop
service mysql restart
二 下面说下文件结构
数据目录 /var/lib/mysql
安装目录 /usr/share/mysql
下面就是我们最关心的配置文件了.默认情况下是没有的,但也可以启动,如果不想使用默认的配置,那需要我们手动拷贝/usr/share/mysql 下面任意一个配置文件my-*.cnf到/etc下,并更名为my.cnf
之后启动,就会生效了.
默认情况下,安装完以后,登陆MYSQL是不需要密码的
mysql -uroot -p
登陆后,需要自己配置远程登陆权限
三 如果需要远程访问,我们还需要做一些工作
1.修改配置文件
[mysqld]
skip-name-resolve
2.本地登陆后,需要把mysql库中的表user里的某一用户的host字段,改为%
一般我们会 update user set host=’%’ where user=’root’ and host=’localhost’
3.其他:编码和引擎
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
default-storage-engine=INNODB

Starting MySQL…Manager of pid-file quit without updating

今天突然发现装在centos上的mysql登录不上了,仔细检查发现是mysqld服务没了,手动启动,报下面的错误,

Starting MySQL…Manager of pid-file quit without updating

到/data/mysql/下看了一下错误日志文件:localhost.localdomain.err, 有下面一段

100408  0:45:32 [Note] Event Scheduler: Purging the queue. 0 events
100408  0:45:32  InnoDB: Starting shutdown…
100408  0:45:32  InnoDB: Shutdown completed; log sequence number 0 383861955
100408  0:45:32 [Warning] Forcing shutdown of 1 plugins
100408  0:45:32 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

100408 00:45:32 mysqld_safe mysqld from pid file /data/mysql/localhost.localdomain.pid ended
100408 00:45:33 mysqld_safe Starting mysqld daemon with databases from /data/mysql
100408  0:45:33 [Note] Plugin ‘FEDERATED’ is disabled.
100408  0:45:33  InnoDB: Started; log sequence number 0 383861955
/usr/local/mysql/bin/mysqld: File ‘./mysql-bin.000056′ not found (Errcode: 13)
100408  0:45:33 [ERROR] Failed to open log (file ‘./mysql-bin.000056′, errno 13)
100408  0:45:33 [ERROR] Could not open log file
100408  0:45:33 [ERROR] Can’t init tc log
100408  0:45:33 [ERROR] Aborting
网上搜了一下,有人说将/etc/my.cnf里面的 skip federated注释掉即可,但我的my.cnf里压根就没有这一项,还有人说/etc/my.cnf中没有设置datadir=/data/mysql,但我看了这一项已经有了。

最后发现一篇文章说data/mysql-bin.index没有删除,data/mysql-bin.index是存放日志文件索引的文件,只删除了日志文件而没有对日志的索引文件做处理显然是不行的。
删除data/mysql-bin.index文件,再service mysqld start就可以了。