Digest of Useful MySql and Unix Commands

[dvdtalk]

While my problem isn't solved, I do want to give back to this forum and some of the people who have given freely of their help.

Here are some useful nuggets I've gotten from posts here. (We're running on Linux so the commands are based on that). Some of this may be basic basic basic stuff, but much of it has been new to me.

1) How to start and stop MySQL

2 ways to do this, best way is to go into your mysql folder (for us this is /usr/local/mysql) and find the file mysql.server

to start mysql do ./mysql.server start
to stop mysql do ./mysql.server stop

*if you have a ton of mysql processes running you may have to do the stop command more than once

you can also go into the bin folder of mysql (for us /usr/local/mysql/bin) and run

./mysqladmin shutdown

I think for us we had to do it as
./mysqladmin -uusername -ppassword shutdown

2) Backing up MySQL

in your MySQL bin directory (for us /usr/local/mysql/bin) run mysqldump:

./mysqldump -uusername -ppassword databasename > /home/root/nameofdumpfile.txt

pick the path that you want to dump the file into.

3) Repairing MySQL Database

First thing you need to do is to backup your database! Then be sure that MySQL is stopped before you do your back up.

For us on the latest version of MySQL to scan your file for errors in your MySQL bin directory (for us /usr/local/mysql/bin) use myisamchk

TO Scan For Errors:

./myisamchk /usr/local/mysql/data/datbasename/*.MIY

To Scan for Errors and Repair Them:

./myisamchk -r /usr/local/mysql/data/datbasename/*.MIY

4Other Tips:

To look at your load on your server you can do one of the following commands:

top (which will show your server load, memory and each process)
top -i (which will only show server load, memory, and active processes - wil ignore idle and zombie processes)
w (shows who's logged in, and what they're doing and load)
uptime (time server has been up and the current load)

free (shows the use of memory on the server, how much is there, what's being used and how much of what's being used is in buffers/cache)

free -b (show same info in bytes)
free -k (show it in K)
free -m (Show it in MB)

Also if you don't know what processor(s) you have you can do a: cat /proc/cpuinfo

Hopefully this list will be of help to someone out there.

Geoff Kleinman
http://www.DVDTalk.com

[JeffG]

What are the commands to change the # of users allowed to connect at a time?

Also, my host tells me to configure my database to "normal connect" instead of "p connect". How do I do that?

[dvdtalk]

That's located in your my.cnf file which you can find in
/etc/my.cnf

if you don't have one there you should create one as it's been valuable for us in configuring MySQL.

Here's the contents of our my.cnf:


# This will be passed to all mysql clients
[client]
#password = my_password
port = 3306
socket = /tmp/mysql.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = max_connections=250
set-variable = join_buffer=6M
set-variable = key_buffer=128M
set-variable = record_buffer=6M
set-variable = sort_buffer=6M
set-variable = max_allowed_packet=1M
set-variable = thread_stack=128K
set-variable = table_cache=250

# Start logging
# log

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=16M


Not sure about Normal v/ p connect

Geoff Kleinman
DVDTalk

[JeffG]

I don't admin my own server. I'm actually a client of a virtual host. Maybe you can help me to get this...

I called my host, asked about MySQL and why I got the "too many connections" error. The representative talked to the server admin, and he said that I can change the MySQL configuration to where it only connects when data needs to be accessed, opposed to having the database running all the time. He said that's "normal connect". By default, my database is always running, and it's called "P connect". He said they don't offer MySQL support, and I should go to MySQL.org, and pay $170 for one email...yeah right, I don't think so.

Maybe he's using the wrong terms from what you know? If you know anything about my problem, let me know.

Is there a way to make each person's connection last for a short amount of time?

Also, I'm asking about upping the connections from a webmaster standpoint, as a web hosting client.

Thanks...

[Martin]

I would recommend getting a program like TextPad or Home Site and do a mas replace on all the vB files.

find: mysql_pconnect
replace with: mysql_connect

if you use one of the programs I recommended above, the whole process takes seconds. I don't know why hosts want people to use connect vs pconnect. it will increase the server load and slow down the sites because it has to reconnect every time a person hits a link.

oh well, what can you do?

[JeffG]

Where do I find mysql_connect? or mysql_pconnect?

I don't see it in any of my vB files...

[John]

It's in db_mysql.php

John

[JeffG]

Thank you so much.

[chrispadfield]

the first bit of info for the new resource section we have talked about:

http://www.vbulletin.com/forum/showthread.php?s=&threadid=7721