Technical article 125755

How do I create a database in MySQL and populate it with some data?

OpenServer 5.0.5OpenServer 5.0.6OpenServer 5.0.7UnixWare 7.0.0UnixWare 7.0.1UnixWare 7.1.0UnixWare 7.1.1UnixWare 7.1.3UnixWare 7.1.4Open UNIX 8

Written · Last revised

Problem

I have installed the enabling components for SCOx or simply wish to know how to create a test database in MySQL, and how to populate this database with some data. Also, how can I backup the database information?

Solution

Prerequisites:
------------
OpenServer6/UnixWare 7.1.4:
--------------------------
See the following technical articles:

TA#126347, "OSR6:How do I install and start to use MYSQL Community v5.0.18? at TA 126347

TA#126471, "How do I install and start to use MYSQL Pro Certified Server v5.0.17c or v4.1.16?" at TA 126471

Other Versions:
--------------
For earlier versions, such as SCO OpenServer 5.0.7 it is recommended
to use OpenServer6 for improved performance and this is a supported
platform from MySQL.
UnixWare 7.1.3:
--------------
MySQL was introduced into UnixWare 7.1.3 with Update Pack 3 and the
SCOx ISO CD v3.23.49

To install, follow the instructions from the appropriate version as detailed above.

Post Install:
------------
   Having installed MySQL and used the "mysqladmin" utility to
   change the root user password with:
# mysqladmin -u root password qwerty78
follow these instructions:

1) First ensure that the database is running.

If not, start the database as per your security requirements.

You could use `/etc/init.d/mysqld start` on UnixWare 7.1.4
for example.
             # ps -ef | grep mysql
root  1697     1   TS  70  0 17:24:15 console  0:00 /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/tiger5.
mysql  1721  1697   TS  59  0 17:24:15 console  0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/

This shows that the server is currently running.

2) Next create a database;
root# mysqladmin -uroot -pqwerty78 create testdb

The above command assumes that your root password is "qwerty78". Change as appropriate.

3) Connect to MySQL, and we Will switch to use your newly created database.

# mysql -uroot -p

and enter the password "qwerty78" when prompted.

You will see something similar to this:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is x to server version:
4.x-standard

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

mysql>

This means that you have successfully connected to the MySQL engine, and it awaits your command.

If not run "status".

Now, let's use our database we've created:

mysql> use testdb
You will see something like this;
Database changed

This means that you have now switched to your newly created database, and have the option to administer it, i.e. Create tables, insert/delete data, etc.

If you get the error message:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Then you will need to set the root password, additionally with the command:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('qwerty78'); Query OK, 0 rows affected (0.00 sec)

To test, exit with "\q" and run:

# mysql -uroot -p
Enter password:
This should be "qwerty78".  You should then see:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 4.1.11

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

mysql> use testdb
Database changed
See: http://dev.mysql.com/tech-resources/articles/mysql_intro.html

4) Create your required tables for your database.

The following command can be used to verify what tables are contained in your database.

mysql> show tables;
Empty set (0.00 sec)

TIP: if you miss off the ";" you will see -> enter ; to end the line.

As is expected in a new database, you have no tables already configured above.

To create a sample table to hold client information;

   mysql>  CREATE TABLE `clients` (
   ->   `id` int(11) NOT NULL auto_increment,
   ->   `name` text NOT NULL,
   ->   `address1` text NOT NULL,
   ->   `address2` text NOT NULL,
   ->   `city` text NOT NULL,
   ->   `postcode/zip` text NOT NULL,
   ->   `county` text NOT NULL,
   ->   `phone` text NOT NULL,
   ->   `fax` text NOT NULL,
   ->   KEY `id` (`id`)
   -> ) TYPE=MyISAM AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.15 sec)

5) Next we insert some test data in to the new "clients" table.

Pasting the following at the "mysql>" prompt....

INSERT INTO clients VALUES (1,'Margaret Smith','123 The Street','Davidsons Mains','Edinburgh','EH4 1PD','Midlothian','+44 131 555 1234','+44 131 666 2345');

INSERT INTO clients VALUES (2,'Jimbo Jones','34 Main Road','Leafy Suburb','Birmingham','BH4 7QD','','+44 666 666 7777','+44 666 666 9999');

INSERT INTO clients VALUES (3,'Bernard Worthington','1 Barnton Avenue','Leafy Suburb','Birmingham','BH14 6QH','','+44 666 666 9999','+44 666 666 0000');

You have now successfully created three records in your new table.

6) Test your new database with SQL statement tests.

First test - show me all data in my "clients" table...

mysql> select * from clients;
+----+---------------------+------------------+-----------
------+------------+--------------+------------+----------
--------+------------------+
| id | name                | address1         | address2
| city       | postcode/zip | county     | phone
| fax              |
+----+---------------------+------------------+-----------
------+------------+--------------+------------+----------
--------+------------------+
|  1 | Margaret Smith      | 123 The Street   | Davidsons
Mains | Edinburgh  | EH4 1PD      | Midlothian | +44 131
555 1234 | +44 131 666 2345 |
|  2 | Jimbo Jones         | 34 Main Road     | Leafy
Suburb    | Birmingham | BH4 7QD      |            | +44
666 666 7777 | +44 666 666 9999 |
|  3 | Bernard Worthington | 1 Barnton Avenue | Leafy
Suburb    | Birmingham | BH14 6QH     |            | +44
666 666 9999 | +44 666 666 0000 |
+----+---------------------+------------------+-----------
------+------------+--------------+------------+----------
--------+------------------+
3 rows in set (0.00 sec)

Second test - just show me the entries from the "clients" tables who live in a "*city*" called "*Birmingham*".

mysql> select * from clients where city like "Birmingham";

+----+---------------------+------------------+-----------
---+------------+--------------+--------+-----------------
-+------------------+
| id | name                | address1         | address2
| *city*       | postcode/zip | county | phone
| fax              |
+----+---------------------+------------------+-----------
---+------------+--------------+--------+-----------------
-+------------------+
|  2 | Jimbo Jones         | 34 Main Road     | Leafy
Suburb | *Birmingham* | BH4 7QD      |        | +44 666
666 7777 | +44 666 666 9999 |
|  3 | Bernard Worthington | 1 Barnton Avenue | Leafy
Suburb | *Birmingham* | BH14 6QH     |        | +44 666
666 9999 | +44 666 666 0000 |
+----+---------------------+------------------+-----------
---+------------+--------------+--------+-----------------
-+------------------+
2 rows in set (0.00 sec)

Your new database is now ready for further test as per your requirements.

7) Dumping the database

Finally, how do you dump all structure and data of this database in to one file for easy recreation at any time? Use the "mysqldump" utility as follows. I have not redirected the output to a file for clarity.

# mysqldump -uroot -pqwerty78 testdb
-- MySQL dump 9.10
--
-- Host: localhost    Database: testdb
-- ------------------------------------------------------
-- Server version       4.0.18-standard
--
-- Table structure for table `clients`
--

CREATE TABLE clients ( id int(11) NOT NULL auto_increment, name text NOT NULL, address1 text NOT NULL, address2 text NOT NULL, city text NOT NULL, postcode/zip text NOT NULL, county text NOT NULL, phone text NOT NULL, fax text NOT NULL, KEY id (id) ) TYPE=MyISAM;

--
-- Dumping data for table `clients`
--

INSERT INTO clients VALUES (1,'Margaret Smith','123 The Street','Davidsons MAINS','Edinburgh','EH4 1PD','Midlothian','+44 131 555 1234','+44 131 666 2345'); INSERT INTO clients VALUES (2,'Jimbo Jones','34 Main Road','Leafy Subarb','Birmingham','BH4 7QD','','+44 666 666 7777','+44 666 666 9999'); INSERT INTO clients VALUES (3,'Bernard Worthington','1 Barnton Avenue','Leafy Suburb','Birmingham','BH14 6QH','','+44 666 666 9999','+44 666 666 0000');

You would of course redirect this output to a file, traditionally with a .sql prefix.

e.g. # mysqldump -uroot -pqwerty78 testdb > testdb.sql

You can use the SQL data directly from this file to manually recreate the database, tables and data contents, or use the "mysqlimport" utility.

See http://dev.mysql.com/doc/ for further details.
TROUBLESHOOTING:
---------------
          - Error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

ERROR 2005 (HY000): Unknown MySQL server host 'locahost' (1)

Reason:

You've entered "mysql" at the prompt without a user name or without the -p password prompt or you are attempting to connect to the database with the wrong password.

- Error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to

Reason:
Either a syntax error or you have not created the table with the
correct fields.  You can use the command "drop table clients" to
remove the table then try again.
- Error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Reason:

/tmp/mysock.sock needs to exist as a socket file.

It is created when "/etc/init.d/mysql start" is called.

If it is not being created, check that port 3306 is available for use with "telnet <localhost> 3306"

The socket file itself is defined in /etc/my.cnf under the 'client' section.

It is likely that "mysql_install_db" has not been run or is unable to
be run.  This can be found in either the /opt/mysql/mysql/bin or
scripts folder.
Check there's no other older data in /var/mysql or /opt/mysql.  You
may wish to remove MySQL, then move these directories to mysql.old.
- Error:

unable to create symbolic link to </opt/mysql/mysql/man/man1/...> UX:pkginstall: ERROR: attribute verification of </usr/man/man1/...> failed pathname does not exist

Reason:
Check /usr/man/man1 exists.

See also

http://dev.mysql.com/tech-resources/articles/mysql_intro.html

TA125427 "What is the SCOx ISO for UnixWare 7.1.3 with Update Pack 3 installed and where can I download it?"

TA125739 "What is the SCOx Web Services Infrastructure CD for UnixWare 7.1.3 and where can I download it?"

TA126111 "Where can I get configuration documentation for MySQL specifically for OpenServer 6, OpenServer 5 and UnixWare 7?

From the Xinuos technical article database, record 125755. Reproduced with permission. The original is at www.sco.com/ta/125755, which is reachable but blocked from search engines.

Next step

Need this fixed rather than explained?

Tell us the release, what the system runs and what it is doing. We will reply by email with what we would do next.

  • You deal with the people who do the work
  • No obligation, and no upgrade required before we will help
  • We tell you plainly if something is not worth doing

Send an enquiry

We reply by email. If a server is down, say so in the first line. You can also write to help@scoexpert.com.