Category Archives: Linux, PostgreSQL, etc.
Notes about using Linux, MySQL, PostgreSQL and other free software, no matter if it’s open sourced or not.
A short note about moving internal Drupal CMS site from test to the production
Target platform: Windows 2003 R2 x64, let’s call the production server PROD (of course, as always, all names, passwords and paths are obfuscated)
Packages installed on PROD:
- MySQL 5.1.28-RC-WINX64.msi (x64) – be careful and pick mirror that offers msi file and not exe. At first I downloaded from local mirror (Slovenia) and got .exe file that I couldn’t run on Windows x64.
- Apache 2.2.10 (win32)
- PHP 5.2.6 (win32) with mysql extension
Prepare empty MySQL database on PROD for Drupal:
cmd> mysqladmin -u root -p create drupal cmd> mysql -u root -p mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON drupal.* TO 'drupal'@'localhost' IDENTIFIED BY 'topsecret'; mysql> flush privileges
Export Drupal database on TEST and import on PROD:
cmd@test> mysqldump -u root -p drupal > drupal.sql cmd@prod> mysql -u drupaluser -p drupal < drupal.sql
Copy Drupal files from Apache\htmldoc on test server to the same directory on production server. Check Drupal configuration file (settings.php) file for MySQL connection (especially for a valid password that is needed to connect to MySQL).
I did the following changes in configuration files:
PHP.INI
I turned on the php_mysql.dll PHP extension: [PHP_MYSQL] extension=php_mysql.dll
All other PHP extensions I turned off with semicolon in front of the extension keyword (;).
Also, I copied libmysql.dll from PHP directory to Apache\bin directory.
Apache (httpd.conf)
I added index.php and index.htm in directoryIndex:
# DirectoryIndex: sets the file that Apache will serve if a directory
# is requested.
DirectoryIndex index.php index.html index.htm
…as well as some aliases for static content, such as:
Alias /doc "X:/DOCUMENTATION"
Options Indexes
AllowOverride None
Order allow,deny
Allow from all
MySQL (my.ini)
I added init-connet to my.ini at the server side, the reason for this is explained in this thread.
[mysqld] init-connect="SET NAMES cp1250"
rlwrap + sqlplus
RLWRAP is one of those low profile tools that are really appreciated when you realize that you’re working on the server/workstation that don’t have them installed. On Windows OS, cmdhere is one such tool that is installed as soon as I logon more than once or twice on the same box. I think rlwrap is one such tool that should be installed on every Linux box – especially if you’re an Oracle DBA/developer working on command line with classic tools, such as sqlplus.
I’m not really spending that much time on Linux command line, but when I do, it’s almost always with Oracle sqlplus and I hate the fact that I could not retrieve previous command(s) by simply pressing the Up arrow key in a way that I’m used to do on Windows command prompt. Thanks to Catherine Devlin presentation I found out about rlwrap – elegant “workaround” (not that this tool is that new, on contrary nowadays it’s really an oldie – it’s just that I was not aware of it; you’ll find plenty of other posts out there on blogosphere – this note is solely for my own bookkeeping!). All we need to do is to install rlwrap that will handle command line history for tools such as sqlplus, rman etc.
After you download rlwrap from: http://utopia.knoware.nl/~hlub/uck/rlwrap/, you can install the tool as root with familiar steps:
# gunzip rlwrap-0.30.tar.gz # tar -xvf rlwrap-0.30.tar # cd rlwrap-0.30 # ./configure # make install
All you need to do is to run sqlplus or rman with read line wrapper:
$ rlwrap sqlplus /nolog or $ rlwrap rman target / nocatalog
A short note about migrating dbaTicket application from MySQL 4.0 to 5.0
dbaTicket is a small internal MS Access application that is using MySQL as a backend.
First, I downloaded and installed MySQL 5.0.45 on Windows XP, then I downloaded and installed MySQL ODBC 3.51.19. During installation I picked up CP1250 for default character set of database. So far so good.
The next step was to prepare database and user who’ll own the dbaticket database:
cmd> mysqladmin -u root -p create dbaticket cmd> mysql -u root -p mysql> grant all privileges on dbaticket.* to 'dbaticket'@'localhost' identified by 'mypwd'; mysql> grant all privileges on dbaticket.* to 'dbaticket'@'%' identified by 'mypwd';
The next step was to export database from production server and import the dump into new MySQL 5.0 (everything below was executed while being logged on Windows XP workstation with MySQL 5.0 running):
E:> mysqldump -u root -p -h prodserver dbaticket > dbaticket.sql After I checked that data in the file is encoded in CP1250 characterset I imported data to MySQL 5 with: E:> cat dbaticket.sql | mysqldump -u root -p dbaticket
The next step was to prepare new ODBC System DSN, named DBATICKET with the MySQL ODBC driver 3.51.19. After that, I had to delete all linked tables from dbaticket.mdb and replace them with the new ones, referring to System DSN: DBATICKET.
At first all seemed OK, application worked. However, I soon noticed that all slovenian letters displayed according to CP1250 code page, except for letters Č and č. Weird. I checked my.ini, I couldn’t find anything wrong – default-character-set was cp1250. Yet, I couldn’t read (nor write) Č and č characters with MS Access.
Quick search on Google revealed solution; you have to add init-connect="SET NAMES cp1250" to [mysqld] section of my.ini.
Example:
# SERVER SECTION # ---------------------------------------------------------------------- # # The following options will be read by the MySQL Server. Make sure that # you have installed the server correctly (see above) so it reads this # file. # [mysqld] init-connect="SET NAMES cp1250" # The default character set that will be used when a new schema or table is # created and no character set is defined default-character-set=cp1250 ...
dbaportal.eu 