Difference between pages "Main Page" and "MySQL"

Latest revision as of 23:33, 6 December 2017

What is MySQL ?

MySQL is the traditional Database software companion to Apache and PHP. MySQL was previously a property of MySQL AB but was acquired by Oracle Corporation in 2010. This created some worries in the OpenSource community, which led to create a fork, MariaDB, which is a plug-in replacement for MySQL and the one included in the Slackware distribution.

Installing MySQL

The quickest way to install MySQL is to NOT install it and use instead MariaDB, included with Slackware. If this is your choice, please note that /usr will be the root directory of the software instead of /usr/local/mysql, and proceed to Running SQL.

apr and apr-util, used by apache, refer to MySQL so the latter must be installed first. Download the latest stable version, choosing "Source Code" when asked to Select platform. Documentation is available from the web site. Here MySQL is compiled with OpenSSL support to be able to encrypt MySQL connections on port 3306 (just in case, as we don't intend to open port 3306 to the external world at the moment).

# groupadd mysql
# useradd -g mysql mysql
# tar -C /usr/local -xvf mysql-x.y.z.tar.gz
# cd /usr/local/mysql-x.y.z
# chown -R root:root .
# less INSTALL-SOURCE
# rm CMakeCache.txt
# make clean
# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DINSTALL_LIBDIR=lib64 \
-DINSTALL_PLUGINDIR=lib64/plugin -DMYSQL_DATADIR=/var/lib/mysql \
-DDEFAULT_CHARSET=latin1 -DDEFAULT_COLLATION=latin1_swedish_ci \
-DMYSQL_UNIX_ADDR=/var/run/mysql/mysql.sock -DWITH_SSL=yes
# make
# removepkg /var/log/packages/mysql-5.1.46-x86_64-2
# make install
# make clean
# cp -R /usr/local/mysql/man /usr/local
# cp support-files/my-default.cnf /etc/my.cnf
# chmod u+x scripts/mysql_install_db
# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/var/lib/mysql

Configuring MySQL

MySQL (5.6.24) works correctly out of the box so there is nothing to do really. In the past we had to edit /etc/my.cnf to uncomment line #skip-networking to prevent MySQL network connections. We also had to include in this file, after the [client] tag a line default-character-set=charset_name (for instance utf8 or latin1) to eliminate this warning when launching a client :

# mysql -u root -p
Enter password: 
mysql: Unknown OS character set 'ISO-8859-15'.
mysql: Switching to the default character set 'latin1'.
Welcome to the MySQL monitor.  Commands end with ; or \g.
. . .

This is no longer necessary, so let us directly update /etc/rc.d/rc.mysqld with new paths and options :

# Start mysqld:
mysqld_start() {
  if [ -x /usr/local/mysql/bin/mysqld_safe ]; then
    # If there is an old PID file (no mysqld running), clean it up:
    if [ -r /var/run/mysql/mysql.pid ]; then
      if ! ps axc | grep mysqld 1> /dev/null 2> /dev/null ; then
        echo "Cleaning up old /var/run/mysql/mysql.pid."
        rm -f /var/run/mysql/mysql.pid
      fi
    fi
    /usr/local/mysql/bin/mysqld_safe --character_set_server=utf8 \
        --basedir=/usr/local/mysql --datadir=/var/lib/mysql \
        --pid-file=/var/run/mysql/mysql.pid $SKIP &
  fi
}

Running SQL

Give execution rights to the rc.mysqld script and launch the daemon :

# chmod u+x /etc/rc.d/rc.mysqld
# /etc/rc.d/rc.mysqld start

Using MySQL

For a quick introduction on MySQL refer to this overview. Otherwise, if you have time to invest, MySQL comes with a very exhaustive documentation, including a tutorial. A few quick notes however :

Mysql defines a root user without an initial password. mysqladmin makes is possible to specify one by typing /usr(/local/mysql)/bin/mysqladmin -u root -p password ‘new-password’. The old password is then requested. Type return (the old password is empty). From that point, the password will need to be specified for every client invocation, for example : "/usr(/local/mysql)/bin/mysql -u root -p mysql_db". "-p" with no value means that the password will be requested interactively. mysql_db is the database name. Not specifying the password on the command line affords avoiding potential disclosure to other users using ps.

You can use mysql to configure itself. Refer to the documentation for the SQL syntax, or use phpMyAdmin, a PHP application to manage MySql from your browser.

To invoke MySQL, use mysql -u user then enter queries at the mysql> prompt, quit to terminate the invocation. Commands can be entered uppercase or lowercase. It is possible to enter formulas, for example select sin(pi()/4), (4+1)*5, current_date;. "\c" at the end of a command affords cancelling.

Queries may be entered on several lines. When awaiting input on a new line, mysql displays -> if a command completion is awaited, '> for a string that began with a ', “> for a string that began with a “, `> for an identifier that began with a `, /*> for a comment that began with a /*.

show databases; to view a list of existing databases. use database (without a semicolon) to use a specific database. GRANT ALL ON database.* TO 'your_mysql_name'@'your_client_host'; must have been used first by the admin to grant access. CREATE DATABASE database; to create a database then USE database.

Alternatively it is possible to specify the database name as the last parameter on the mysql command eg mysql -h host -u user -p database. select database(); affords viewing which database is in use.