So, I had to set-up master-slave replication. Here’s how you do it: Why would one want to set-up this? Easy. 1)Spread the read load across multiple servers: you can use the MySQL master for writing (inserts, updates) and the slave(s) for reading (SELECT) This should speed up things nicely for your application. 2)Do...
Posts Tagged ‘ mysql ’
Enabling the MySQL slow query log
If you have a slow web app or website it might be because of unoptimized sql queries. You want to track and fix those and the mysql slow query log is very helpful here. Enabling the slow query log MySQL prior to 5.1.0 requires adding a setting to the MySQL my.cnf file and restart...
cbnet Ping Optimizer is killing a site
Some wise soul decided many moons ago that installing the aforementioned plugin on a site I host. The wise soul is long gone (admining websites for ngo’s as a volunteer is no fun to many ) but the site was getting more and more sluggish lately. I decided to clean a bit the many...
Optimizing all mysql tables (from all databases)
You want to optimize mysql tables from time to time in order to reduce fragmentation. Here’s an easy way to do it automatically (put it in a script and run it from cron for example) View Code BASH1 mysqlcheck -Aop -h hostname -u user -pPASSWORD What all those mean: –all-databases, -A; Check all tables...
Automatically sync photos from digikam to your mobile phone
I shoot a lot of pictures. My collection keeps growing (for example ~90 Gb right now) and since last year I discovered the joy of using digikam to tag and organize them. I’m using the beta 2.0.0 version and use mysql to store it’s databases (as opposed to the default sqlite) as it’s easier...
MariaDB vs MySQL short benchmark
Considering switching from MySQL to MariaDB? Me too. I wanted to see if I can look towards any performance improvements (or drops). I don’t have high level needs, I’m sure more comprehensive tests exist. This is simple run-all-tests.pl from sql-bench suite. The test machine was a CentOS VM with 2Gb RAM and 2 CPU...
Mysql authentication issues
Ever happened to you that you create a user with privileges to connect from any host but it won’t connect from the localhost? Like this: View Code BASH1 2 3 4 5 6 7 8 9 10 11 12 [root@18969_1_490528 tmp]# mysql -u test_user -puser_pass ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using...
MYSQL: set first letter of field to uppercase
I recently had to set values of a set of values from a column from lowercase to Firstuppercase. Nothing easier, run the following code on the database: View Code SQL1 2 3 UPDATE `affected_table` SET `field_in_question` = CONCAT(UPPER(LEFT(`field_in_question`, 1)), SUBSTRING(`field_in_question`, 2))
Authentication problems with postfix, courier, mysql and virtual domains
I outgrew my shared hosting and decided to go for a dedicated virtual server. I chose Linode but I will leave that for another article. I decided to use postfix + courier + mysql and virtual domains for my mail setup. Everythig was configured fine and dandy according to the documentation. But I kept...
Export a MySQL database using the command line.
Sometimes it’s easier to just drop to the command line then browse for fancy tools like phpMyAdmin. This is useful, especially for quick jobs. View Code BASH1 mysqldump -u user -ppassword your_database > database_backup.sql Of course you need to replace: user – mysql username password – the password your_database – the database you want...
Follow Me
Recent Comments