lizongbo at 618119.com 工作,生活,Android,前端,Linode,Ubuntu,nginx,java,apache,tomcat,Resin,mina,Hessian,XMPP,RPC

2010年11月17日

在Ubuntu10.04上通过mysql源代码进行编译安装

Filed under: Linux,mysql — 标签:, , , , — lizongbo @ 00:10

在Ubuntu10.04上通过mysql源代码进行编译安装MySQL Community Server

操作系统是linode上的Ubuntu 10.04,
MySQL Community Server目前最新正式版为:mysql 5.1.51 GA.
源代码下载地址为:
http://www.mirrorservice.org/sites/ftp.mysql.com/Downloads/MySQL-5.1/mysql-5.1.51.tar.gz

来源:http://www.mysql.com/downloads/mirror.php?id=394595#mirrors
http://www.mysql.com/downloads/

1.先下载源代码到本地:
root@618119.com:/usr/local/app# wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.51.tar.gz/from/http://www.mirrorservice.org/sites/ftp.mysql.com/
2.解压mysql-5.1.51.tar.gz到/usr/local/app/mysql-5.1.51:
root@618119.com:/usr/local/app# tar -zxvf mysql*
3.进入解压得到的源码目录中:
root@618119.com:/usr/local/app# cd mysql*
4.运行configure进行检查:
root@618119.com:/usr/local/app/mysql-5.1.51# ./configure -prefix=/usr/local/app/mysql -with-charset=utf8 -with-collation=utf8_general_ci -with-extra-charsets=latin1
提示信息:checking for termcap functions library… configure: error: No curses/termcap library found
5.安装libncurses5-dev进行解决:
root@618119.com:/usr/local/app/mysql-5.1.51# sudo apt-get install libncurses5-dev
6.在编译安装nginx时候已经安装了 gcc libc6-dev build-essential 组件
直接开始运行make编译源代码:
root@618119.com:/usr/local/app/mysql-5.1.51# make
7.等待一段时间后,源码编译完成,运行make install进行安装:
root@618119.com:/usr/local/app/mysql-5.1.51# make install
8.复制系统服务脚本:
root@618119.com:/usr/local/app/mysql# cp /usr/local/app/mysql/share/mysql/mysql.server  /etc/init.d/mysql
9.再运行命令: sudo /usr/sbin/update-rc.d -f mysql defaults,将mysql安装为默认服务。
root@618119.com:/usr/local/app/mysql# sudo /usr/sbin/update-rc.d -f mysql defaults
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LANG = “zh_CN.UTF-8”
are supported and installed on your system.
perl: warning: Falling back to the standard locale (“C”).
Adding system startup for /etc/init.d/mysql …
/etc/rc0.d/K20mysql -> ../init.d/mysql
/etc/rc1.d/K20mysql -> ../init.d/mysql
/etc/rc6.d/K20mysql -> ../init.d/mysql
/etc/rc2.d/S20mysql -> ../init.d/mysql
/etc/rc3.d/S20mysql -> ../init.d/mysql
/etc/rc4.d/S20mysql -> ../init.d/mysql
/etc/rc5.d/S20mysql -> ../init.d/mysql
10.复制默认配置到/etc目录:
root@618119.com:/usr/local/app/mysql# cp /usr/local/app/mysql/share/mysql/my-medium.cnf /etc/my.cnf
11:添加名字为mysql的用户:
root@618119.com:/usr/local/app/nginx# sudo adduser –system –no-create-home –disabled-login –disabled-password –group mysql
12.初始化数据库(如果没初始化,启动mysql会提示“Manager of pid-file quit without updating file”):
root@618119.com:/usr/local/app/mysql/bin# ./mysql_install_db –user=mysql
Installing MySQL system tables…
101022 15:24:16 [Warning] ‘–skip-locking’ is deprecated and will be removed in a future release. Please use ‘–skip-external-locking’ instead.
OK
Filling help tables…
101022 15:24:16 [Warning] ‘–skip-locking’ is deprecated and will be removed in a future release. Please use ‘–skip-external-locking’ instead.
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/local/app/mysql/bin/mysqladmin -u root password ‘new-password’
/usr/local/app/mysql/bin/mysqladmin -u root -h 618119.com password ‘new-password’

Alternatively you can run:
/usr/local/app/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr/local/app/mysql ; /usr/local/app/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/app/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/local/app/mysql/bin/mysqlbug script!

13.启动mysql数据库.
root@618119.com:/usr/local/app/mysql/bin# sudo /etc/init.d/mysql start

14.命令行进入数据库进行测试:
root@618119.com:/usr/local/app/mysql/bin# ./mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.51-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| mysql              |
| test               |
+——————–+
3 rows in set (0.00 sec)

mysql> quit
Bye

15.运行mysql自带的测试脚本需要等的时间比较久:
root@618119.com:/usr/local/app/mysql# cd /usr/local/app/mysql/mysql-test ; perl mysql-test-run.pl
测试结果是1000多项只有600项通过:
main.mysql_client_test                   [ fail ]
Test ended at 2010-10-22 15:35:39

CURRENT_TEST: main.mysql_client_test
mysqltest: At line 15: command “$MYSQL_CLIENT_TEST –getopt-ll-test=25600M >> $MYSQLTEST_VARDIR/log/mysql_client_test.out.log 2>&1” failed

Output from before failure:
exec of ‘/usr/local/app/mysql/bin/mysql_client_test –defaults-file=/usr/local/app/mysql/mysql-test/var/my.cnf –testcase –vardir=/usr/local/app/mysql/mysql-test/var –getopt-ll-test=25600M >> /usr/local/app/mysql/mysql-test/var/log/mysql_client_test.out.log 2>&1’ failed, error: 256, status: 1, errno: 0

The result from queries just before the failure was:
SET @old_general_log= @@global.general_log;
SET @old_slow_query_log= @@global.slow_query_log;

– saving ‘/usr/local/app/mysql/mysql-test/var/log/main.mysql_client_test/’ to ‘/usr/local/app/mysql/mysql-test/var/log/main.mysql_client_test/’

Only  653  of 1415 completed.
mysql-test-run: *** ERROR: Not all tests completed
166.使用符号链接方便直接运行 mysql命令:
root@618119.com:/usr/local/app/mysql# sudo ln -s /usr/local/app/mysql/bin/mysql /usr/bin/
root@618119.com:/usr/local/app/mysql# sudo ln -s /usr/local/app/mysql/bin/mysqladmin /usr/bin/

开始在resin上安装phpMyAdmin:
phpMyAdmin最新版本 3.3.7:
下载地址:http://nchc.dl.sourceforge.net/project/phpmyadmin/phpMyAdmin/3.3.7/phpMyAdmin-3.3.7-all-languages.tar.gz
来源:
http://www.phpmyadmin.net/home_page/index.php

下载后解压到:lizongbo@618119.com:/etc/init.d$ cd /usr/local/app/resin/webapps
然后改名:
lizongbo@618119.com:/usr/local/app/resin/webapps$ mv phpMyAdmin-3.3.7-all-languages/ phpMyAdmin

尝试resin最新版跑phpMyAdmin跑不了。
还是想办法配置nginx吧,可以参考:
http://www.linuxidc.com/Linux/2010-03/25161.htm
mysql JDBC驱动:
最新正式版为:Connector/J 5.1.13
下载地址:http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector- java-5.1.13.tar.gz/from/http://www.mirrorservice.org/sites/ftp.mysql.com/
来源:http://www.mysql.com/downloads/connector/j/

root@618119.com:/usr/local/app# wget http://www.mirrorservice.org/sites/ftp.mysql.com/Downloads/Connector-J/mysql-connector-java-5.1.13.tar.gz
root@618119.com:/usr/local/app# tar -zxvf mysql-c*
复制jdbc驱动到resin的lib目录:
root@618119.com:/usr/local/app/mysql-connector-java-5.1.13# cp /usr/local/app/mysql-connector-java-5.1.13/mysql-connector-java-5.1.13-bin.jar /usr/local/app/resin/lib

2009年01月7日

blog的myql数据库居然挂了。

Filed under: mysql — 标签:, — lizongbo @ 22:52

前晚升级了博客到WordPress 2.7,当时还好好的。

结果今晚打开的时候,被转到了安装页面。
http://618119.com/wp-admin/install.php
老是去安装界面,
点安装却提示:WordPress database error: [Table ‘./sevtiger_lzb/wp_options’ is marked as crashed and should be repaired]
在网上找到说mysql的索引坏了,要执行myisamchk -c -r *.MYI。
于是下载了gtalk,登录上去生活点滴的cosβ请教了,
然后到phpmyadmin的管理界面,选中所有的表,然后选修复表,
就修复好了。
但是相关文章页面还是打不开。
html方式访问不了, http://618119.com/archives/2008/12/29/110.html
The requested URL /archives/2008/12/29/110.html does not exist.
经提示到后台管理的设置中,重新保存了永久链接设置,
总算都恢复了。

2008年06月27日

mysql 5.0 在插入记录的时候遇到 Incorrect string value这个错误

Filed under: mysql,数据库 — 标签: — lizongbo @ 20:24

修改 C:\Program Files\mysql\mysql Server 5.0\my.ini

mysqlmysqld的 default-character-set为gbk,

my.ini节选如下:
[mysql]

default-character-set=gbk

[mysqld]
port=3306

basedir=”C:/Program Files/mysql/mysql Server 5.0/”

#Path to the database root
datadir=”C:/Program Files/mysql/mysql Server 5.0/Data/”
default-character-set=gbk
default-storage-engine=MyISAM

然后重启数据库,重新创建数据库即可。

创建的数据库步骤如下:

在heidisql里,先运行  set names ‘gbk’,

然后运行建表sql。

然后就可以看到表的字段和内容,均为中文显示了。

其它关于mysql 的笔记:

1.在命令行下, 用 mysql -uroot连接到mysql服务器
2.SHOW DATABASES; 查看当前服务器已经有的数据库名字。
3.use test 表示连接到名叫”test”的数据库
4.show table;
查看该数据库中的表名。
5.查询记录
select * from topic limit 10,3;
表示从topic中的第11条记录开始,取出三条,
6 show status;
查看数据库配置的一些信息。
7.查看数据库版本
SELECT VERSION();

8.创建数据库
CREATE DATABASE `testtable`; /*!40100 CHARACTER SET utf8 COLLATE utf8_general_ci */

9.创建表

CREATE TABLE `testtable`.`testtablemsg` (`testtabletype` VARCHAR (50)
DEFAULT ‘fc_ssq’ NOT NULL, `testtableday` VARCHAR (50) DEFAULT ‘08024’ NOT NULL,
`testtablename` VARCHAR (50) DEFAULT ‘福’ NOT NULL, PRIMARY KEY(`testtabletype`,`testtableday`))
COMMENT = “开奖公告” TYPE = InnoDB /*!40100 DEFAULT CHARSET utf8 COLLATE utf8_general_ci */;

10.查看表字段
SHOW /*!32332 FULL */ COLUMNS FROM `testtablemsg`;
11.查看主键信息
SHOW KEYS FROM `testtablemsg`;

12.导入sql到指定的数据库:

mysql -uroot -p testtable < /data/lizongbo_tmp/import/testtable.sql

13.查看所有连接

SHOW FULL PROCESSLIST;

14.导入sql文件的时候遇到错误信息

ERROR 1153 at line 6388: Got a packet bigger than ‘max_allowed_packet’

然后查看对应参数:

mysql> show VARIABLES like ‘%max%’;
+———————————+———————-+
| Variable_name                   | Value                |
+———————————+———————-+
| ft_max_word_len                 | 254                  |
| ft_max_word_len_for_sort        | 20                   |
| innodb_max_dirty_pages_pct      | 90                   |
| innodb_max_purge_lag            | 0                    |
| max_allowed_packet              | 1047552              |
+———————————+———————-+

于是修改
mysql> set max_allowed_packet=4047552;
Query OK, 0 rows affected (0.00 sec)

Older Posts »

Powered by WordPress