MYSQL On Mac OS X
Santosh Patnaik, Apr 19, 2005
What is MySQL
MySQL is a very popular, open source (free), relational database management system (RDBMS) that uses Structured Query Language (SQL) for adding, accessing, and processing data in a database. It is downloadable from MySQL website (www.mysql.com) for various operating systems, including the Mac OS X. MySQL is coded in the ANSI C computer language.
What are the components of MySQL
MySQL system contains a server (the mysqld 'daemon'), the databases themselves (including a user database), log files, mysql clients, etc. The MySQL clients include mysqladmin (to administer the system) and mysql (to access the databases). All these are installed when 'MySQL' is installed.
MySQL installation locations on Mac
As of Mac OS 10.3, the mysql/ directory is a hidden directory (inside /usr/local/). The clients are inside the /bin subdirectory and databases themselves are inside the /data subdirectory. The daemon is in usr/include/mysql/. The /usr/local/mysql/ directory should have these ownership/permissions - owner - root, read, write and execute; group - wheel, read and execute; and, world, read and execute. These are usually set properly during the installation.
What is a MySQL database made of
A MySQL database is made of tables and a table is made of records (rows) containing information in fields dictated by columns. One cannot use an application like Excel, Word or Access to see the contents of the database. However, one can 'see' the databases through web-based applications like !phpMyAdmin (www.phpmyadmin.net) or applications like Navicat (www.navicat.com).
Can MySQL databases be exported
There are third-party applications (like !phpMyAdmin, Navicat, etc.) and plug-ins to export MySQL data (entire database or just one table at a time).
How is a MySQL database backed up
The MySQL client mysqldump can be used to backup databases. The generated file can be used to load the databases on a separate system. Read more here. There are other ways to backup too - such as using !phpMyAdmin, Navicat, etc.
How are MySQL databases accessed
Programming languages such as C, Java, Perl, PHP, etc., can access MySQL databases (query the database, modify it, etc.). Interfaces like web-pages and some 'regular' applications like Navicat (www.navicat.com) use these languages to access MySQL.
What are MySQL users
MySQL users can access the MySQL system. A user is really an application, and it has username, password and host settings. The host setting identifies where the user is connecting from (a certain IP address, localhost [local computer], etc.).
The root user is the super-user and is identified as 'root.' It is different from the Mac OS root user. As of April 2005, when MySQL is installed the root password is empty. For security reasons, it should be set - see MySQL commands
Other users have different types of privileges. E.g., they may have been created or modified to have access to only one table in one of the databases in the MySQL.
The MySQL server or daemon
Most MySQL installations will be such that the daemon starts up automatically when the computer starts. The server is installed as owned by a hidden user (named mysql) by the Mac OS. So, it is 'started' by Mac OS user 'mysql' and not 'the' Mac OS user (such as you) who is using the computer.
To stop the server, see under mysqladmin below.
The server uses TCP/IP port 3306 for networking.
Using the MySQL clients
The clients are installed in /usr/local/mysql/bin/. The clients are run through Terminal application by going to the install location. E.g., typing usr/local/mysql/bin/mysql. However, most installations create symbolic links so typing just mysql does the same thing.
In Terminal application, type -
mysqladmin -u root -p
Enter root password when prompted (this root is different from Mac OS root)
This client allows you to change passwords, create new users, shut down mysqld, etc.
Also see MySQL commands
In Terminal application, type -
mysql -u * -pwhere * is the username
Enter root password when prompted
This client allows a user to access the databases and tables therein. The privileges (e.g., whether a user can delete a record) may have been restricted.