|
Contact Systems  Misc |
Table of ContentsIntroductionThe standard MySQL™ install is run as a self-contained DBMS that needs an administrator to add users and grant access. For instructional purposes we've written some scripts that allow you to run your own MySQL server (under your own user id), listening on its own socket/port, with its own database area, and with the database tables, permissions, and personal configuration all maintained by you. This is done with the command mysqlctl. mysqlctl installThe command mysqlctl install creates a mysql data directory (~/mysql-data) and creates the initial tables. When prompted enter a database password of your choosing. Remember this password as you will need it to run all the mysql and mysqladmin commands. jdash@ix: ~ 16$ mysqlctl install This script is designed to setup an individual mysql server Database password (DO NOT use your unix password): Verify password: Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Preparing procs_priv table Installing all prepared tables jdash@ix: ~ 17$ mysqlctl startThe command jdash@ix: ~ 17$ mysqlctl start Port 3602 busy, trying 3524 Started mysqld on port 3524 jdash@ix: ~ 18$ ((sourcend:) !! [[#stop]] mysqlctl stop The command @@mysqlctl stop@@ will stop your mysql server. (:source lang=text -getcode:) jdash@ix: ~ 21$ mysqlctl stop Stopping mysqld process 1767 Check the running serverYou can use jdash@ix: ~ 18$ mysqlctl status mysqld (pid 1767) listening on ix:3524 If the server is not running the results will look like this: jdash@ix: ~ 22$ mysqlctl status no .pid file for ix:3524 You can also use jdash@ix: ~ 19$ mysqladmin -p version Enter password: mysqladmin Ver 8.41 Distrib 5.0.24a, for sun-solaris2.9 on sparc Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.0.24a-log Protocol version 10 Connection ix via TCP/IP TCP port 3524 Uptime: 2 min 35 sec Threads: 1 Questions: 1 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 5 Queries per second avg: 0.006 This should show you that your data directory, port, socket, and any other settings you've made are being correctly read. Note: when you start the mysql shell or many of the mysql administrative programs, you will need to use the "-p" flag so that you will be prompted for your database password. Make a new database for your applicationThe organizational concept each mysql server works under is that it has a number of databases, and each database will in turn have a number of possibly related tables. Access rights can be different for different databases or even different tables. As set up, there is a database called "mysql" which has the tables for permission and access granting. You can see the tables with the command: jdash@ix: ~ 20$ mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 5.0.24a-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SHOW TABLES FROM mysql; +-----------------+ | Tables_in_mysql | +-----------------+ | columns_priv | | db | | func | | host | | tables_priv | | user | +-----------------+ 6 rows in set (0.01 sec) mysql> So to start with, you'll want to create a new database: mysql> CREATE DATABASE my_db; Query OK, 1 row affected (0.00 sec) Tell mysql to use your new database: mysql> use my_db; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed then give it one or more tables: mysql> CREATE TABLE names ( first_name text, last_name text ); Query OK, 0 rows affected (0.02 sec) Now, to add some names to the names table: mysql> insert into names values ("Jo Don","Dash"); Query OK, 1 row affected (0.00 sec) mysql> select * from names; +------------+-----------+ | first_name | last_name | +------------+-----------+ | Jo Don | Dash | +------------+-----------+ 1 row in set (0.00 sec) That's the basic database creation commands. DebuggingYou can enable query logging by uncommenting the line general-log and error logging the line log-error in your .my.cnf file. # This will be passed to all mysql clients [client] port = 1234 socket = /home/users/jdash/mysql-data/mysqld_sock host = ix-trusty #password = my_password # The MySQL server [mysqld] port = 3753 socket = /home/users/jdash/mysql-data/mysqld_sock datadir = /home/users/jdash/mysql-data pid-file= /home/users/jdash/mysql-data/.pid # default is to only bind localhost # bind-address = 0.0.0.0 # for all mysql versions #skip-innodb # for mysql-5.5 and above #default-storage-engine=myisam # for mysql-5.6 and above default-tmp-storage-engine=myisam # uncomment the following line for logging #general-log #log-error Notes about our local installationOther stuff to do...The details of maintaining a mysql server are beyond the scope of this document. However, the official mysql docs are actually fairly understandable, accurate, and up to date. The mysqlctl program is a shell script located in /local/bin/mysqlctl. It creates a file in your home directory named .my.cnf. See AlsoManual Pages
External Links |
