Questions[mysql5](server)

After creating a self-signed SSL certificate, I have configured my remote MySQL server to use them (and SSL is enabled)

I ssh into my remote server, and try connecting to its own mysqld using SSL (MySQL server is 5.5.25)..

mysql -u <user> -p --ssl=1 --ssl-cert=client.cert --ssl-key=client.key --ssl-ca=ca.cert
Enter password: 
ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

Ok, I remember reading theres some problem with connecting to the same server via SSL. So I download the client keys down to my local box, and test from there...

mysql -h <server> -u <user> -p --ssl=1 --ssl-cert=client.cert --ssl-key=client.key --ssl-ca=ca.cert 
Enter password: 
ERROR 2026 (HY000): SSL connection error

Its unclear what this "SSL connection error" error refers to, but if I omit the -ssl-ca, then I am able to connect using SSL..

mysql -h <server> -u <user> -p --ssl=1 --ssl-cert=client.cert --ssl-key=client.key 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.25 MySQL Community Server (GPL)

However, I believe that this is only encrypting the connection, and not actually verifying the validity of the cert (meaning I would be potentially vulnerable to man-in-middle attack)

The SSL certs are valid (albeit self signed), and do not have a passphrase on them. So my question is, what am I doing wrong? How can I connect via SSL, using a self signed certificate?

MySQL Server version is 5.5.25 and the server and clients are CentOS 5.

Thanks for any advice

Edit: Note that in all cases, the command is being issued from the same directory where the ssl keys reside (hence no absolute path)

Edit (in response to mgorven): ca.cert is the Certificate Authority certificate, which is supposed to tell mysql that my certificate authority is trusted.

The config from my.cnf is

[mysqld]
ssl-ca=/etc/ssl/mysql/ca.cert
ssl-cert=/etc/ssl/mysql/server.cert
ssl-key=/etc/ssl/mysql/server.key

I also tried adding ssl-cipher=DHE-RSA-AES256-SHA but have since removed it as it didn't help.

MySQL details:

MySQL username: root
MySQL password: z

To run MySQL query without login, the command is:

mysql -u root -pz -e "mysql-query-here"

So, if I want to create database with name db1, it will become:

mysql -u root -pz -e "CREATE DATABASE db1 CHARACTER SET utf8 COLLATE utf8_bin"

Now, I want to run this query for database db1

INSERT INTO `role_permission` VALUES (1,'create about_us content','node')

I dont know how to make it works. I've tried many combinations without luck.

I have a project coming up where one of the requirements will be to cache what's essentially read-only data in order to lessen the strain on the db. I'm just a bit confused on how caching works with a db.

How does caching work? I've seen something called query caching, but does that cache query results? Or just the queries? And isn't a view a form of caching anyway?

I'm just looking for a nudge in the right direction. The project will use MySQL 5.1 for the store, so any links that can clear up my confusion would be a big help. A general Google search has only provided me with query caching, and with my lack of knowledge in this area, I'm not sure if that's the direction I should go.

After setting root password why does MYSQL still allow me to login without a password from the command line? I can type "mysql" at a root unix prompt and it asks for no password and still allows me root access. I am not understanding why "mysql -u root" is not NOW asking me for the password that I set on the account.

Also, I cannot login to the mysql from a remote machine as 'root'. Didn't I configure it correctly below? I get the error: "Host...is not allowed to connect to this MySQL server. Didn't I configure it for '%' ?

Here is my user table:

mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| %         | root |                                           |
| 127.0.0.1 | root |                                           |
| ::1       | root |                                           |
| localhost |      |                                           |
| %         |      |                                           |
+-----------+------+-------------------------------------------+
6 rows in set (0.00 sec)

So... yesterday I received an "after the fact email" about a campaign that has started for one of the services that I run. Now the DB server is getting hammered, hard, to the tune of about 300mb/min in binary logging for the replicate. As you could imagine, this is chewing up space at a fairly tremendous rate.

My normal 7 day expiry of binary logs just isn't cutting it. I've resorted to truncating logs to just the last for 4 hours with(I'm verifying that replication is up to date with mk-heartbeat):

PURGE MASTER LOGS BEFORE DATE_SUB( NOW(), INTERVAL 4 HOUR);

I'm just running that from cron every few hours to weather the storm, but it made me question the minimum value for expire_logs_days. I haven't come across a value that is less than 1, but that doesn't mean that it isn't possible. http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_expire_logs_days gives the type as being numeric, but doesn't indicate if it's expecting integers.