Database Servers

• Welcome to linuxzoo
  • System Administration
    • Extras
      • Database Servers

Advantages of a database server

A database server is a common requirement in many computer environments. A database server typically offers the following advantages to an enterprise:

• The database offers a single repository of data for an organisation.
• Having a single stored database makes it easier to maintain the accuracy and currency of the data.
• A single database makes it easier to protect against loss of data due to hardware failure.

• The central server may be critical to the function of many parts of the organisation. When the database goes down the workers may as well go home.
• The server can be a bottle-neck. By insisting on a single database we are concentrating the load on a single machine.

We can protect ourselves from some of these problems:

• We can take regular backups - these can allow us to revert the whole database to a known "good" state.
• Using replication we can almost eliminate the danger of data loss or significant down-time.
• It may be possible to spread the processing load across a number of machines.

Facilities provided by a database server

There are a number of vendors of database products. These include the industry heavy-weights such as Oracle and IBM's DB2 down to cheap and relatively light-weight products such as MySQL.

We should expect the following facilities from a relational database server:

• User management
• Backup and restore
• Support for transactions and locking
• High performance and scalability

The MySQL distribution is included in many Linux installations. Your virtual machine should have the package.

Managing the server

The mysql daemon is called mysqld - this needs to be running before users can log into the client program mysql.

In Linux the MySQL server may be started by the command

/etc/init.d/mysqld start

chkconfig mysqld on

The script that sits in the /etc/init.d/ directory responds to the parameters start, stop and restart.

MySQL users

MySQL maintains it's own list of user accounts - these are independent of the operating system.

This is a common strategy, shared by Oracle. IBM's DB2 relies on the Linux to authenticate users, Microsoft's SQL Server permit users to be authenticated either by the operating system or using it's own authentication scheme.

• user root - this is the system administrator.
• the anonymous user
• the database test
• the database mysql - this contains the meta data - it includes lists of users, tables and other important details.

The root user has permission to do pretty much anything to the database. By default this user has no password; this poses a serious security problem. The root password should be set as soon as possible.

It is easiest to manage users from within mysql. Use mysql -u root -p to start the mysql client.

Fedora Core release 2 (Tettnang)
Kernel 2.6.7-1um on an i686
login: root
Password:
Last login: Thu Sep 16 13:26:26 on tty1
You have new mail.
-bash-2.05b# /etc/init.d/mysqld start
Initializing MySQL database:    OK  ]
Starting MySQL:    OK  ]
-bash-2.05b# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.23.58

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

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql>

mysql> DESCRIBE mysql.user;
+-----------------+-----------------+------+-----+---------+-------+
| Field           | Type            | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host            | char(60) binary |      | PRI |         |       |
| User            | char(16) binary |      | PRI |         |       |
| Password        | char(16) binary |      |     |         |       |
| Select_priv     | enum('N','Y')   |      |     | N       |       |
| Insert_priv     | enum('N','Y')   |      |     | N       |       |
| Update_priv     | enum('N','Y')   |      |     | N       |       |
| Delete_priv     | enum('N','Y')   |      |     | N       |       |
| Create_priv     | enum('N','Y')   |      |     | N       |       |
| Drop_priv       | enum('N','Y')   |      |     | N       |       |
| Reload_priv     | enum('N','Y')   |      |     | N       |       |
| Shutdown_priv   | enum('N','Y')   |      |     | N       |       |
| Process_priv    | enum('N','Y')   |      |     | N       |       |
| File_priv       | enum('N','Y')   |      |     | N       |       |
| Grant_priv      | enum('N','Y')   |      |     | N       |       |
| References_priv | enum('N','Y')   |      |     | N       |       |
| Index_priv      | enum('N','Y')   |      |     | N       |       |
| Alter_priv      | enum('N','Y')   |      |     | N       |       |
+-----------------+-----------------+------+-----+---------+-------+
17 rows in set (0.00 sec)

mysql>

We may explicitly refer to another machine as Host, we may use the wild card % to refer to any machine (local or not).

For discussion

Mostly the User,Host complication may be ignored, we may either:

• always use localhost as the Host - this will prevent remote access
• always use the wild card % as the Host - this permits access from any machine

Users may be created by adding a row to the table mysql.user.

Commands to manage users

These commands should be executed from within mysql by the mysql user root.

INSERT INTO mysql.user(user,host) VALUES('scott','localhost');
FLUSH PRIVILEGES;

Add a user scott. This permits scott to log in from the server machine only, he may not log in from any other computer.
The new user will not be recognised by the system unless notified using the FLUSH PRIVILEGES command (or the daemon is restarted).

SET PASSWORD FOR root@localhost = PASSWORD('secure');

set the password for the user root when using mysql locally.
The function PASSWORD is a hash function. This scrambles the text so that not even the root can read a password.

SELECT user, host, password FROM mysql.user;

View the fields user host and password from the table user in the database mysql

DROP USER scott

You know what that does.

Commands to create data

These commands should be executed at the mysql prompt. Some of them can only be executed by root, some may be executed by normally users if they have been given appropriate permissions.

SHOW DATABASES;

List the databases

CREATE DATABASE example;

Tables must be in a database - this creates a new database.

USE example;

Set database example as the current, default database.

CREATE TABLE table1(field1 INTEGER);

Create a table called table1 in the current database. This new table has one field called field1

INSERT INTO table1 VALUES (1);

INSERT INTO table1 VALUES (2);

Add rows to table1.

SELECT * FROM table1;

View all rows and columns in table1

Commands to control access

We use GRANT and REVOKE to assign privileges to users. We can use a wild card * for tables or databases, we can specify PUBLIC to represtent any user. The privileges can be things such as: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALL PRIVILEGES,

GRANT SELECT ON table1 TO scott@localhost;

Permit the user scott to read the data with a SELECT command. If the user scott does not exist it will be created.

REVOKE INSERT ON table1 FROM scott@localhost;

Prevent scott from adding data to the table.

GRANT UPDATE ON database1.* TO scott@localhost;

Allow scott to change any existing records in any table in database1.

GRANT CREATE ON * TO scott@localhost

Allow scott to create tables in the current database.

GRANT ALL ON scott.* TO scott@localhost

Allow scott full controls to database scott.

Backing up and restoring databases

Backup

We can backup our data at various different levels. We can choose to backup all databases or just one database or just a selection of tables within one or more databases.

If we want to backup data on users and permissions we should include this database.

We may take backups in two different ways:

Coping files

The directory /var/lib/mysql/ contains a directory for each database. Each table is held across three files in this directory - we may simply copy these files or directories. We should take some precautions however:

• We should stop the database server, or at least lock the tables before copying - if a change were to happen during the copying we could corrupt the data or the indexes.
• We should take care to preserve the file permissions. These files are owned by user mysql. Tools such as tar may be used to preserve these details.
• The directory /var/lib/mysql need not be correct. We can find the correct directory using the unix command mysqladmin variables

Use mysqldump

The tool mysqldump generates the SQL commands required to recreate the data. It includes many options to specify the what should be recorded. We may:

• Backup the table structures only
• Backup the data only
• Backup all database, just some database or just some tables.

Restore

Restore from files

To restore data from files we need simply copy the files into the correct location (by default /var/lib/mysql). We would normally stop the server before doing this.

Restore from mysqldump

As mysqldump produces sql we need simply execute the output to restore the data. This may be done by redirecting the dump file into mysql:

mysql -u root < /var/tmp/dumped.sql

mysql -u root -D database1 < /var/tmp/dumped.sql

-bash-2.05b# mysqldump
mysqldump  Ver 8.23 Distrib 3.23.58, for redhat-linux-gnu (i386)
By Igor Romanenko, Monty, Jani & Sinisa
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

  -A, --all-databases   Dump all the databases. This will be same as
                        --databases with all databases selected.
  -a, --all             Include all MySQL specific create options.
  -#, --debug=...       Output debug log. Often this is 'd:t:o,filename`.
  --character-sets-dir=...
                        Directory where character sets are
  -?, --help            Display this help message and exit.
  -B, --databases       To dump several databases. Note the difference in
                        usage; In this case no tables are given. All name
                        arguments are regarded as databasenames.
                        'USE db_name;' will be included in the output
  -c, --complete-insert Use complete insert statements.
  -C, --compress        Use compression in server/client protocol.
  --default-character-set=...
                        Set the default character set
  -e, --extended-insert Allows utilization of the new, much faster
                        INSERT syntax.
  --add-drop-table      Add a 'drop table' before each create.
  --add-locks           Add locks around insert statements.
  --allow-keywords      Allow creation of column names that are keywords.
  --delayed-insert      Insert rows with INSERT DELAYED.
  --delete-master-logs  Issue RESET MASTER on the master just after taking
                        the dump, and before unlocking tables.
                        This will automatically enable --first-slave.
  --master-data         This will cause the master position and filename to
                        be appended to your output, before unlocking tables.
                        This will automatically enable --first-slave.
  -F, --flush-logs      Flush logs file in server before starting dump.
  -f, --force           Continue even if we get an sql-error.
  -h, --host=...        Connect to host.
  -l, --lock-tables     Lock all tables for read.
  --no-autocommit       Wrap tables with autocommit/commit statements.
  -K, --disable-keys   '/*!40000 ALTER TABLE tb_name DISABLE KEYS */;
                        and '/*!40000 ALTER TABLE tb_name ENABLE KEYS */;
                        will be put in the output.
  -n, --no-create-db    'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;'
                        will not be put in the output. The above line will
                        be added otherwise, if --databases or
                        --all-databases option was given.
  -t, --no-create-info  Don't write table creation info.
  -d, --no-data         No row information.
  -O, --set-variable var=option
                        give a variable a value. --help lists variables
  --opt                 Same as --add-drop-table --add-locks --all --quick
                        --extended-insert --lock-tables --disable-keys
  -p, --password[=...]  Password to use when connecting to server.
                        If password is not given it's solicited on the tty.

  -P, --port=...        Port number to use for connection.
  -q, --quick           Don't buffer query, dump directly to stdout.
  -Q, --quote-names     Quote table and column names with `
  -r, --result-file=... Direct output to a given file. This option should be
                        used in MSDOS, because it prevents new line '\n'
                        from being converted to '\n\r' (newline + carriage
                        return).
  -S, --socket=...      Socket file to use for connection.
  --tables              Overrides option --databases (-B).
  -T, --tab=...         Creates tab separated textfile for each table to
                        given path. (creates .sql and .txt files).
                        NOTE: This only works if mysqldump is run on
                              the same machine as the mysqld daemon.
  -u, --user=#          User for login if not current user.
  -v, --verbose         Print info about the various stages.
  -V, --version         Output version information and exit.
  -w, --where=          dump only selected records; QUOTES mandatory!
  -X, --xml             dump a database as well formed XML
  -x, --first-slave     Locks all tables across all databases.
  EXAMPLES: "--where=user='jimf'" "-wuserid>1" "-wuserid<1"
  Use -T (--tab=...) with --fields-...
  --fields-terminated-by=...
                        Fields in the textfile are terminated by ...
  --fields-enclosed-by=...
                        Fields in the importfile are enclosed by ...
  --fields-optionally-enclosed-by=...
                        Fields in the i.file are opt. enclosed by ...
  --fields-escaped-by=...
                        Fields in the i.file are escaped by ...
  --lines-terminated-by=...
                        Lines in the i.file are terminated by ...

Default options are read from the following files in the given order:
/etc/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit
--no-defaults           Don't read default options from any options file
--defaults-file=#       Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read

Possible variables for option --set-variable (-O) are:
max_allowed_packet    current value: 25165824
net_buffer_length     current value: 1047551

Incremental backups

We can start mysqld with the bin-log option. This causes files such as /var/lib/mysql/host-1-17-bin.001 to be created. These represent all changes made to the database since the server started.

To ensure that mysqld starts with this option we can add the line log-bin to the [mysqld] section of the file /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin

With this logging in place we can use mysqldump to create a snap shot of the database, we can then take regular backups of the bin files. With both of these we can recreate the current state of the database. The advantage of the imcremental backup is that even if the whole database becomes huge we need only backup the changes as represented in the bin files.

We can recover the contents of the bin log as SQL using the command mysqlbinlog, this output can be piped into mysql.

mysqlbinlog /var/lib/mysql/*bin.[0-9][0-9][0-9]|mysql -u root -psecure

Linuxzoo created by Gordon Russell.
@ Copyright 2004-2023 Edinburgh Napier University