Posts Tagged ‘MySQL’

High Availability Linux Web Server Example

Just a quick example of setting up a Linux HA failover environment for an Apache/MySQL web server. This runs through a Ubuntu installation, however RedHat shouldn’t vary too much. 2.11.1.24 – fixed IP of server1 2.11.1.25 – fixed IP of server2 2.11.1.30 – apache site1 (virtual IP) 2.11.1.31 – apache site2 (virtual IP) Make sure […]

Configuring MySQL over SSL for client connections

Useful for setting up encrypted connections between client and server.  Parts of this can be substituted if you wished to get a certificate issued from a trusted CA. 1. Check that SSL has been compiled in MySQL on the server: SHOW VARIABLES LIKE ‘have_openssl’; or mysql –ssl –help If it says disabled or yes then […]

MySQL reset lost root password

/etc/init.d/mysql.server stop /usr/local/mysql/bin/safe_mysqld –user=mysql –skip-grant-tables –skip-networking & /usr/local/mysql/bin/mysql mysql> UPDATE mysql.user SET Password=PASSWORD(‘MyNewPass’) WHERE User=’root'; mysql> FLUSH PRIVILEGES; mysql> exit /etc/init.d/mysql.server restart Probably a good idea to clear the MySQL history afterwards or pull in the update from a txt file and delete!

Purge MySQL Binary Logs

If you have binary logging enabled in MySQL, for example if you are using replication, there is a good chance that these binary logs will grown pretty big and fill the disk.  It’s a good idea to set some limits in my.cnf expire-logs-days    = 20 max_binlog_size         = 104857600 If you find the need to purge […]

osCommerce broken after PHP/MySQL 5upgrade

osCommerce doesn’t seem to be very compatible with MySQL 5 and will most likely generate a load of errors from its SQL statements. Upgrading to the latest version is recommended and should fix this.  For heavily modified versions this may not be posible and will require some hacking to fix: add () to from tables […]

MySQL Replication over SSL

##Tutorial for MySQL Replication over SSL (one-way)## ##################################################### ##Check that SSL has been compiled in SSL on both the master and the client: SHOW VARIABLES LIKE ‘have_openssl'; or mysql –ssl –help If it says disabled or yes then its fine.  If it says no then an SSL enabled version of MySQL needs to be installed. […]

MySQL repair corruption

for db: mysqlcheck -uadmin -p$(cat /etc/psa/.psa.shadow ) –databases databasename –repair for table: check table tablename; repair table tablename; repair can lose data so a restore from backup may be necessary.

MySQL Log slow queries

edit /etc/my.cnf: log-slow-queries=/var/log/mysql-slow.log set-variable=long_query_time=6 restart mysqld

MySQL Log queries not using indexes

# Edit /etc/my.cnf log-queries-not-using-indexes = /var/log/mysql-indexes.log

MySQL set Fulltext Min_Word_Len

add the following to my.cnf under [mysqld] ft_min_word_len=2 stop mysql myisamchk –recover –ft_min_word_len=2 /var/lib/mysql/*/*.MYI start mysql