北國狼人的部落格

隨筆-159 評論-114 文章-7 trackbacks-0

安裝MySQL之前，先檢查是否已經有沒用的舊有MySQL

不卸載，有亂七八糟的問題會出現

rpm -qa|grep mysql -i

發現老版本的，就卸載

rpm -e --nodeps --allmatches mysql-4.1.22-2.e14

下面開始安裝，強制安裝丫的

rpm -ivh MysqlServer.rpm --nodeps --force

安裝順序 server dev share client debuginfo

netstat -nat

察看是否啟動。

配置/etc/my.cnf

# The following options will be passed to all MySQL clients

[client]

#password = your_password

port = 3306

socket = /data/mysql/database/mysql.sock

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

port = 3306

socket = /data/mysql/database/mysql.sock

datadir = /data/mysql/database/

pid-file = /data/mysql/database/mysql.pid

skip-locking

key_buffer_size = 2048M

max_allowed_packet = 2M

table_open_cache = 512

sort_buffer_size = 8M

read_rnd_buffer_size = 4M

query_cache_size = 1M

query_cache_type = 1

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 16

thread_cache_size = 8

innodb_file_per_table

read_buffer_size = 4M

myisam_sort_buffer_size = 4M

myisam_recover

myisam_repair_threads=4

server-id = 1

log-bin=/data/mysql_backup/binlogs/database

max_binlog_size = 512M

expire_logs_days = 3

long_query_time = 2

log-slow-queries = slow.log

log-queries-not-using-indexes

event_scheduler=1

# Uncomment the following if you are using InnoDB tables

innodb_data_home_dir = /data/mysql/database/

innodb_data_file_path = ibdata1:20M;ibdata2:10M:autoextend

#innodb_log_group_home_dir = /var/lib/mysql/

#innodb_log_arch_dir = /var/lib/mysql/

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 10000M

innodb_additional_mem_pool_size = 4M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 8M

innodb_log_buffer_size = 4M

#innodb_flush_log_at_trx_commit = 1

#innodb_lock_wait_timeout = 50

##

#interactive_timeout=6048000

wait_timeout=31536000

interactive_timeout=31536000

connect_timeout=60

max_connect_errors=100000000

max_connections=50

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

[isamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer_size = 128M

write_buffer_size = 128M

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer_size = 128M

write_buffer_size = 128M

[mysqlhotcopy]

interactive-timeout

service mysql stop

如果目錄改變了，那么需要重新建立新數據庫目錄

那么my.cnf中兩個目錄的屬主需要配置，否則也會有問題

chown -R mysql.mysql /data/mysql

chown -R mysql.mysql /data/mysql_backup

mysql_install_db --defaults-file=/etc/my.cnf --user=mysql

輸出正常自己察看

看看/data/mysql/下面的database中是否有ib開帶頭的幾個文件，ibdata1 ibdata2 log 等。InnoDB數據實際都放在一個大文件里面

service mysql start

新install_db 的不需要密碼，如果mysql -uroot -p
再回車，需要數據密碼才能登錄，那肯定不對。

檢查ps -efww|grep mysql 看看進程使用的my.cnf是否正確。

正常登錄后
mysql> show engines;

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

無需密碼。我們設置一下。

注意MySql 5.1 使用old_password函數設置，java程序才是對，否則程序總是會報Access Denied，其實密碼是對的。格式如下

use mysql;

update user set password=old_password("new_pass") where user="root";修改密碼

flush privileges; 刷新權限

mysql>GRANT ALL ON *.* TO 'root'@'%'

mysql>flush privileges;

設置一下其他地址使用root 登錄mysql的遠程訪問權限

nohup mysql -uroot -ppassword --default-character-set=utf8 < /data/t.sql &

ps -aux|grep mysql

什么時候Done了，就導完數據。

ll -h /data/mysql/database

mysql -uroot -p -S /data/mysql/database/mysql.sock

有時候，會mysql等不了或者關閉不了，必須找到驅動才能關閉或者登陸

mysqladmin -uroot -p -S /data/mysql/database/mysql.sock shutdown

備份數據dump操作

mysqldump -uroot -p -S /data/mysql/database/mysql.sock -F -B tianwen tianwen_log -Q > /data/bak0408.sql

=================

優化Innodb 的my.cnf

grep innodb /etc/my.cnf

innodb_additional_mem_pool_size = 20M

innodb_buffer_pool_size = 12G

innodb_data_file_path = ibdata1:4G;ibdata2:50M:autoextend

innodb_file_io_threads = 8

innodb_thread_concurrency = 16

#innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 16M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

#innodb_lock_wait_timeout = 120

innodb_file_per_table=1

innodb_rollback_on_timeout

max_connections = 1200 也最好設置大點，大規模操作數據會比較順暢。

posted on 2010-04-10 13:38 北國狼人的BloG 閱讀(201) 評論(0) 編輯收藏

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理

<

2010年4月

>

日

一

二

三

四

五

六

28

29

30

31

1

2

3

4

6

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

1

2

3

4

5

6

7

8

常用鏈接

留言簿(33)

隨筆分類

隨筆檔案

文章分類

文章檔案

Java學習論壇

3D至尊寶

常用鏈接

留言簿(33)

隨筆分類

隨筆檔案

文章分類

文章檔案

Java學習論壇

Java最牛網站

搜索

最新評論

閱讀排行榜

評論排行榜