Created at:

Modified at:

Mysql notes

Basic setup on NetBSD

(2024-04-09)

Using NetBSD 9.3 and MySQL 8.0.33.

After installing and starting mysqld daemon on NetBSD, it will print a temporary password to root user.

So, the first thing you have to do is to login using the temporary password and change it: 

$ mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

MySQL: How to Reset the Root Password

To create a database in a MySQL server, just use the mysqladmin command:: 

    $ mysqladmin -u root -p create databasename

Now login as the root user of the server:: 

    $ mysql -u root -p databasename

Then create the user that will own the database:: 

    CREATE USER 'wp'@'localhost' IDENTIFIED BY 'wp123456';

Then, give permissions to that user on the database:: 

    GRANT ALL ON databasename.* TO 'username'@'localhost';

Then, just connect to the database with your new user:: 

    $ mysql -p -u username databasename

Generic notes

(2012-09-20)

A note on user creation and its privileges

I'm not used to databases, but I use them since I develop applications that must store their data somewhere.

When configuring the database for koha, I did:: 

    CREATE DATABASE koha;
    CREATE USER kohaadmin IDENTIFIED BY 'katikoan';
    GRANT ALL ON koha.* TO 'kohaadmin'@'localhost';
    FLUSH PRIVILEGES;

Koha

But, after creating the kohaadmin user, I couldn't connect to the database. Logging in as root and seeing the mysql.User table showed the problem:: 

    mysql> use mysql;
    (...)
    Database changed

    mysql> SELECT Host, User, Password FROM user WHERE User = 'kohaadmin';
    +-----------+-----------+-------------------------------------------+
    | Host      | User      | Password                                  |
    +-----------+-----------+-------------------------------------------+
    | %         | kohaadmin | *9BBFD4AD8554387AE1366CC62F4C576DDC495EF1 |
    | localhost | kohaadmin |                                           |
    +-----------+-----------+-------------------------------------------+
    2 rows in set (0.00 sec)

Nice, so we don't have a password configured for kohaadmin for localhost access? Our last GRANT command was wrong, we should have used clause IDENTIFIED BY in this command:: 

    GRANT ALL ON koha.* TO 'kohaadmin'@'localhost' IDENTIFIED BY 'katikoan';
    FLUSH PRIVILEGES;

Now, it looks ok:: 

    mysql> SELECT Host, User, Password FROM user WHERE User = 'kohaadmin';
    +-----------+-----------+-------------------------------------------+
    | Host      | User      | Password                                  |
    +-----------+-----------+-------------------------------------------+
    | %         | kohaadmin | *9BBFD4AD8554387AE1366CC62F4C576DDC495EF1 |
    | localhost | kohaadmin | *9BBFD4AD8554387AE1366CC62F4C576DDC495EF1 |
    +-----------+-----------+-------------------------------------------+
    2 rows in set (0.00 sec)

Listing users

(2021-06-02)

To list all users, do:: 

    mysql> SELECT user, host FROM mysql.user;

How To Use MySQL DROP USER to Delete a User Account in MySQL

Dropping users

(2021-06-02)

To drop a user, you must specify both the user and the host part for DROP USER:: 

    mysql> DROP USER user@localhost;

Creating index in a better way

(2014-10-01)

One can use the following well-known syntax to create an index:: 

    CREATE INDEX indexname ON tablename(columnname);

In MySQL this approach has a problem because MySQL copies the table to a temporary file, create the index and copy it back. So, if you need to create several indexes, a copy of the entire table is needed for each index creation. This can be **really slow** on huge tables.

The InnoDB Engine has an approach. You can create several index at once, using the ALTER TABLE command:: 

    ALTER TABLE T1 ADD INDEX (B), ADD UNIQUE INDEX (C);

This will still copy all the table file elsewhere, but only once, create all the index and copy it back.

InnoDB Fast Index Creation

Troubleshooting

MySQL don't start up because of permission problems

(2012-10-26)

When seeing the logs of mysql, you might see something like that:: 

    121326 18:13:19  mysqld started
    121026 18:13:19  InnoDB: Operating system error number 13 in a file operation.
    InnoDB: The error means mysqld does not have the access rights to
    InnoDB: the directory.
    InnoDB: File name ./ibdata1
    InnoDB: File operation call: 'create'.
    InnoDB: Cannot continue operation.

You should check the permissions for all the files mysql will access match the user that runs mysql. In my environment, for example, there is a user "mysql" that runs the daemon. So, supposing database is installed in /usr/upkg/var/mysql, we do:: 

    # cd /usr/upkg/var/mysql
    chown -R mysql:mysql *

But the error persists. I discovered that the problem was that I forgot to change the ownership of the parent directory. Then:: 

    # chown mysql:mysql /usr/upkg/var/mysql

And it worked.

ERROR 2006 (HY000) at line 76: MySQL server has gone away

(2016-09-27)

I got an error similar to that when trying to import a file generated by mysqldump. The reason was that, on the record located at line 76, a big chunk of data couldn't get inserted to the database and the server just refused. In the article linked below I discovered that it was due the max_allowed_packet variable of the [mysqld]] section on MySQL settings, located at the my.cnf file. I changed it to 64M, restarted the server and the restore operation worked.

Solution To MySQL error #2006 in XAMPP

Note that, on NetBSD, the file my.cnf doesn't exist by default. Just create it on /usr/pkg/etc/my.cnf with the following contents:: 

    [mysqld]
    max_allowed_packet = 64M