MySQL Web Hosting
Sometimes we need to tune or optimize mySQL server configuration for better performance.
Before making any changes, I strongly recommend that you back up the file, so that you can restore it in case the service does not restart or any other problem happens:
Use the following Config for my.cnf.
1. Take backup of existing config with
cp /etc/my.cnf my.cnf.bak
2. vi /etc/my.cnf
3. Remove the whatever entry are there.
4. Add the following.
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_user_connections=25
max_connections=500
interactive_timeout=10
wait_timeout=10
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
myisam_sort_buffer_size=64M
old-passwords = 1
[mysql.server]
user=mysqlbasedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192
[mysqldump]
quickmax_allowed_packet=16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
And restart mysql on server.
# /scripts/restartsrv_mysql
MySQL and databases
FTP (File Transfer Protocol), is a way of moving files from one machine to another. FTP is the most common way to update a web site with contribute.
To connect to an FTP server with Contribute, you must have the following basic information:-
1.The site address:- Example: http://www.domain.com . The site address is the URL which you use to navigate to a site in a browser. When you put a URL address into a browser, the browser looks to the HTTP server for that information.
2.The FTP server address:- The FTP server address may have an ftp prefix, such as ftp.domain.com, or it may look like a standard url, such as www.domain.com .
3.User name and password:- FTP account information which may differ from other server login information.
4.The location of the web files on the server:- You have to upload the web files for your website to the web directory for your website . For example, /public_html/ means that your website’s files are contained in a folder called public_html, which is in the root folder(/home/username) of the website.
FTP related issues, MySQL and databases
The most important properties required to connect DB from scripts ( PHP,CGI or Perl ) are
database server : localhost
database name : cpanelusername_testdb
database username : cpanelusername_dbuser
database user password : *****
Note: DB server name is always “localhost”. It means they connect to DB only from scripts inside the server. If the client trying to connect DB from his pc ( remotely ) then he will get errors becuase we have blocked mysql remote port 3306. Clients are not allowed to connect DB remotely.
MySQL and databases
You can use following code to setup daily cron job to take database backup.
| Quote: |
—————————————–
year=$(date|cut -c25-2
month=$(date|cut -c5-7)
day=$(date|cut -c9-10)
mysqldump -hlocalhost -uusername –password=password –opt yourdbname > /home/username/www/voice/db_voice$year$month$day.sql
echo .
echo DATABASE BACKUPS COMPLETE
echo .
—————————————– |
The parts in red you will have to change to your username, password and dbname.
Please note that from mysqldump………. to db_voice$year$month$day.sql is all one line.
The parts in blue is the path/filename of the file you want to save the DB to, in my case db_voice. The $year$month$day just ensures that each backup file has a different name, i.e. the date of the backup is part of it as well.
The cronjob that calls the backup script is:
15 0 * * * /home/username/www/voice/backup_dbs > /dev/null
This runs the backup at 00:15 each morning and the orange is the full path to the script above, so you will need to change this to point to the location of your script.
MySQL and databases
You can find phpMyAdmin
cpnel -> MySql -> [Go to Bottom of the page] phpMyAdmin
One of the most common tasks a database administrator performs is the frequent backup of the data in a database. phpMyAdmin simplifies this task via its “Export” module, which makes it possible to export the structure and contents of a database or table to a variety of different formats, either for backup or to migrate data from one database to another.
This module is accessible from the main application page, via the “Export” link.
Using this module is simplicity itself - all you have to do is select the databases to be exported, the format of the output file, and whether you would like the output file to contain the table definitions, the table contents or both. A number of different output formats are available - you can have your data exported as regular SQL queries, as comma-separated values, or as LaTEX-formatted data. You can also choose to either view the exported output directly in your browser (useful if you’re trying to quickly obtain information on the structure of a table) or save it to a file for archival or import into another application.
As with all good export modules, the output file created by phpMyAdmin can be imported back into the application to recreate the database. Simply select the appropriate database (or create a new one as needed), switch to the SQL tab of the database module, and give phpMyAdmin the location of the output file.
The application will now automatically take care of detecting the file format and importing its contents into the selected database.
That’s It. Now there is no need to use mysqldump command from the shell.
MySQL and databases
Do following things for
Warning: mysql_connect(): Can’t connect to local MySQL server through
socket ‘/var/tmp/mysql.sock’
edit /etc/my.cnf
Make sure that it has the following:
code:——————————————————————————–[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
socket=/tmp/mysql.sock——————————————————————————–
If not, then add the above. Save, close and then execute
service mysql start
service chksrvd start
ln -s /tmp/mysql.sock /var/tmp/mysql.sock
ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock
MySQL and databases
Make sure root pass is in /root/.my.cnf
This is usually the first mysql root pass the box was given once cpanel is installed.
If it still does not work, you will need to reset the mysqld root pass.
First, stop the mysqld, usually
/etc/rc.d/init.d/mysql stop
Then stop chkservd to keep it from interfering with mysqld while you work on it
with /etc/rc.d/init.d/chkservd stop
Start up mysqld , but, without the grant tables,
mysqld –skip-grant-tables -u mysql &
Then change the pass..
mysql -u root mysql
UPDATE user SET Password=PASSWORD(’new_password’) WHERE user=’root’;
FLUSH PRIVILEGES;
Now you just
killall -9 mysqld
and start it up normally with it’s safe_mysqld script..
safe_mysqld –skip-grant-tables & /usr/local/cpanel/startup
MySQL and databases
/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
error: ‘Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)’
Check that mysqld is running and that the socket: ‘/var/lib/mysql/mysql.sock’ exists!
Solution::
step by step
login via ssh
run the commands as follows:
killall mysqld
safe_mysqld –skip-grant-tables &
/usr/local/cpanel/startup
Then login into WHM reset the MySQL root
password.
go back to ssh…
killall mysqld
/etc/rc.d/init.d/mysql start
MySQL and databases
1) Login to shell as root
2) And give the following commands :
a) mysql
b) use mysql
c) update user set Create_tmp_table_priv=’Y’ where Create_tmp_table_priv=’N';
d)update user set Lock_tables_priv=’Y’ where Lock_tables_priv=’N';
e) update `user` set Create_tmp_table_priv = ‘N’, Lock_tables_priv = ‘N’ where user <> ‘root’;
flush privileges;
e) quit
3) service mysql restart
MySQL and databases