Grants are how MySQL handles user permissions to databases. See our article here for information on showing the grants. To delete grants, you can directly modify the mysql.user table: mysql> select User,Host from mysql.user; +——+—————————+ | User | Host | +——+—————————+ | are | 10.10.10.10 | | are | 10.10.10.11 | | are | 10.50.100.0/255.255.255.0 […]

If you need to add an auto increment field to a MySQL table, you can use the mysql command to do this: $ mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 622439 to server version: 4.0.22-standard Type ‘help;’ or ‘\h’ for help. Type […]

To back up all databases on a particular server, use the mysqldump command: $ mysqldump –all-databases -p | bzip2 -c > databasebackup.sql.bz2 Enter password: $ This will put all of the databases in a compressed bzip2 file. Transfer the file to your other server and decompress: $ bzip2 -d databasebackup.sql.bz2 $ ls data*.sql databasebackup.sql $ […]

SQLite has a build in import command for importing records from text files. We used a tab for a field separator: $ sqlite3 database.db SQLite version 3.1.3 Enter “.help” for instructions sqlite> .separator “\t” Now that we have set our field separator, we can import: sqlite> .import ex.txt tablename sqlite>

Many MySQL system variables can be set on the fly at the command line, without restarting the server. This is a great thing at times, like when that new code which was so thoroughly “tested” winds up consuming five times the database connections as the old version. Oops! Well, within the limits of the resources […]

As we mentioned in Using MCJ to Practice SQL Commands, the MCJ has the ability to run queries directly. We had a bunch of configuration and compile options that now should run with type for the build tab2. We can change all of the chroot types to prechroot with the SQL command: update journal set […]

To grant access to a database for a particular user and subnet, you can use this command: mysql> grant all privileges on arewedown.* to are@’10.50.100.0/255.255.255.0′ identified by ‘arepass’; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql> select User,Host from mysql.user; +——+—————————+ | User | Host […]

We tried a bunch of different methods to try and access a MySQL database via ODBC for our AreWeDown service. The code below is available here in a fully functioning application that starts as a service. The most reliable method we found to connect to MySQL databases from Windows clients via a VB app appears […]

To capture the error of a PHP query against a MySQL database, use this syntax: $result=mysql_query($query); if (!$result) { die(‘Invalid query: ‘ . mysql_error()); }

phpMyAdmin is a web-based tool for managing MySQL databases. The installation mainly consists of extracting the distribution and editing the database authentication information. In this article we will secure phpMyAdmin using a change of the directory name and a .htaccess file. First off, let’s extract the package: [root@srv-5 webroot]# ls php* phpMyAdmin-2.6.4-pl3.tar.bz2 [root@srv-5 webroot]# tar […]

To determine what databases and tables are stored in your MySQL database, log in using the mysql client: [usr-1@srv-1 ~]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 4.1.12 Type ‘help;’ or ‘\h’ for help. Type ‘\c’ […]

An autoincrement field is already included with SQLite. Select ROWID from a table to get the unique row identification. Here is some REALBasic code that selects the ROWID along with the other columns in the table and displays it in a ListBox: UtilSet.LBAP.DeleteAllRows db= New REALSQLdatabase dbFile = GetFolderItem(“mcj.rsd”) db.DatabaseFile=dbFile If db.Connect() then rs = […]

SQLite is a small, embeddable SQL database engine. Its licensing allows it to be included in commercial products and modified without publishing the source code. REALbasic uses SQLite as its included database engine. REALbasic has a query tool; however, it is nice to have the full-blown sqlite client. Let’s download the tarball and configure, compile, […]

Mountain Climbing Journal (MCJ) is a journal application that includes a SQL Tool to query the database using an embedded SQLite database engine. MCJ is a single executable. In this example we are using the GNU/Linux version, but there are versions for Microsoft Windows and Mac OS X as well: [usr-1@sv-1 sqlart]$ ls mcjl [usr-1@sv-1 […]

Mountain Climbing Journal (MCJ) is a journal application that includes a SQL Tool to query the database using an embedded SQLite database engine. In Part 1 we created an initial database. Now, let’s add some records. The empty cup-like icon that is yellow, below, is what is used to add a blank record. It turns […]

Mountain Climbing Journal (MCJ) is a journal application that includes a SQL Tool to query the database using an embedded SQLite database engine. In Part 2 we created some records. Now, let’s do some queries. Our first query will be to select all fields in all records in the journal table: select * from journal […]

One excellent MySQL administration tool is phpMyAdmin. The installation is straightforward, just extract and follow Documentation.txt. One gotcha is that it requires an Apache server with php support, and MySQL, of course. It is fairly easy to install Apache and MySQL, but getting everything installed in the right order to run a PHP web site […]

To communicate with MySQL via Perl, use the DBI and DBD-MySQL modules. With Red Hat 8.0, just install perl-DBD-MySQL-2.1017-3 and perl-DBI-1.30-1 off of the CDs. Why would you want to do such a thing? Well, say you had a tab delimited text file: sam 33 red sarah 32 blue ed 23 black russell 13 yellow […]

Uncompress the source: [src]# tar -xzf mysql-3.23.40.tar.gz Change to mysql source root, and add mysql group and user: [src]# cd my* [mysql-3.23.40]# groupadd mysql [mysql-3.23.40]# useradd -g mysql mysql Run configure, specifying installation directory as /usr/local/mysql: [mysql-3.23.40]# ./configure –prefix=/usr/local/mysql creating cache ./config.cache checking host system type… i586-pc-linux-gnu . . . Remember to check the platform […]

To back up MySQL, log on as the root user and run: mysqldump –opt -u user -p dbname > {path}/backup.sql To restore the database run: mysql -u user -p dbname < {path}/backup.sql