Mark's Technomusings

Using MySQL from Microsoft Access

Using MySQL from Microsoft Access describes in great detail (and good screenshots) how to connect to a MySQL database from MS Access. This allows you to add/delete/insert data using Access as a front end. All of the changes actually take place on the MySQL server.

---

Before you do this, you will need to make sure that MS Access has permissions to connect to MySQL. Set up those permissions this way:

[root@server root]# mysql -u root mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 519002 to server version: 4.0.24-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> INSERT INTO user VALUES ('IP address or host name','username',password('some_password_in_cleartext'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);
Query OK, 1 row affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)

There is a slight change that you might need to make a change to the db:

INSERT INTO db VALUES (IP address or host name','database name','username','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y');

Mysql binary log maintenance

I've been noticing that my /usr partition has been slowly getting more and more full. I finally figured out why. I had enabled MySQL to create binary logs, and the log file was growing quite large:

[root@ns data]# ls -sh *bin*
4.0K ns-bin.001  920K ns-bin.004   12M ns-bin.007
4.0K ns-bin.002  4.0K ns-bin.005   69M ns-bin.008
644K ns-bin.003  4.0K ns-bin.006  4.0K ns-bin.index
[root@ns data]# mysqladmin flush-logs
[root@ns data]# ls -sh *bin*
4.0K ns-bin.001  920K ns-bin.004   12M ns-bin.007  4.0K ns-bin.index
4.0K ns-bin.002  4.0K ns-bin.005   69M ns-bin.008
644K ns-bin.003  4.0K ns-bin.006  4.0K ns-bin.009

As you can see, right now, the current logfile is ns-bin.008 and has a size of 69MB! After running mysqladmin flush-logs, there is now a new 4.0kb ns-bin.009 file....and the ns-bin.008 file is still there...but I can cancel it and free up 69MB of space.