Difference between revisions of "Converting the webwork database from the latin1 to the utf8mb4 character set"

From WeBWorK_wiki
Jump to navigation Jump to search
Line 60: Line 60:
   
 
Assuming the webwork database is using the latin1 character set, continue reading these instructions.
 
Assuming the webwork database is using the latin1 character set, continue reading these instructions.
  +
  +
===Check what the default character set is for MySQL on your new or upgraded server===
  +
  +
Log into mysql on your new or upgraded server. Depending on your OS and mysql version, you will either use the command
  +
  +
$ mysql -u root -p
  +
Enter Password: <mysql root password>
  +
  +
or
  +
  +
$ sudo mysql
  +
[sudo] password for wwadmin: <wwadmin password>
  +
  +
You should see something very similar to
  +
  +
Welcome to the MySQL monitor. Commands end with ; or \g.
  +
Your MySQL connection id is 4
  +
...
  +
  +
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  +
  +
Now issue the following mysql commands:
   
 
==Backup the webwork database==
 
==Backup the webwork database==

Revision as of 18:41, 8 March 2021

These instructions explain how to convert the webwork database from the latin1 to the utf8mb4 character set.

Terminal Window Notation

In a terminal window some commands will have to be run as root whereas others should be run as a regular user. We will use # to indicate that the command is to be run as root e.g.

# perl -MCPAN -e shell

and $ to indicate that the command is to be run as a normal user e.g.

$ mysql -u root -p 

Of course you can use sudo to run commands as root from a standard command prompt. In general we will use the same notation used in Installation Manual for 2.12 on Ubuntu 16.04.

Preliminaries

Check What the Current Character Set Is

Before we begin let's make use sure the webwork database is using the latin1 character set.

Log into mysql. Depending on your OS and mysql version, you will either use the command

$ mysql -u root -p 
Enter Password: <mysql root password>

or

$ sudo mysql
[sudo] password for wwadmin: <wwadmin password>

You should see something very similar to

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
...

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Now issue the following mysql commands:

mysql> Use webwork;
...
Database changed


mysql> SELECT @@character_set_database;

If the webwork database is using the latin1 character set you will see:

+--------------------------+
| @@character_set_database |
+--------------------------+
| latin1                   |
+--------------------------+
1 row in set (0.04 sec)

Now exit MySQL

mysql> exit
Bye
$

Assuming the webwork database is using the latin1 character set, continue reading these instructions.

Check what the default character set is for MySQL on your new or upgraded server

Log into mysql on your new or upgraded server. Depending on your OS and mysql version, you will either use the command

$ mysql -u root -p 
Enter Password: <mysql root password>

or

$ sudo mysql
[sudo] password for wwadmin: <wwadmin password>

You should see something very similar to

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
...

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Now issue the following mysql commands:

Backup the webwork database

IMPORTANT: Do not skip this step.

First we create a directory to hold the backup file and cd to the new directory

$ mkdir mysql_backups
$ cd mysql_backups

From now on in these instructions I will assume you are using the older method of logging into mysql as root using a password rather than the newer method of sudoing into mysql. If this is not the case, use sudo wherever the mysql password is used in these instructions.

Now use the mysqldump command to create the backup file.

 $ mysqldump -u root -p webwork > webwork_backup.sql
Enter Password: <mysql root password>

After the process finishes, the file webwork_backup.sql will be located in the mysql_backups directory.

Restore the webwork database

Hopefully this will not be necessary but here is how the restore the backedup webwork database.

First we have to log into MySQL and then drop and recreate the webwork database.

$ mysql -u root -p 
Enter password: <mysql root password>
mysql> drop database webwork;
mysql> CREATE DATABASE webwork;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, LOCK TABLES ON webwork.* TO 'webworkWrite'@'localhost';

Now exit MySQL

mysql> exit
Bye
$

cd to the mysql_backups directory the contains the backup sql file.

$ cd
$ cd mysql_backups

and use the following command to restore the database

mysql -u root -p webwork < webwork_backup.sql
Enter password: <mysql root password>

Now connect to WeBWorK and everything should be restored.