Home Page
Archive > Posts > 2016 > February
Archive > Posts > 2016 > February

Warning: you do not have javascript enabled. This WILL cause layout glitches.

Database list of countries and currencies

I had the need for a project recently to have a database list of countries and currencies. I ended up pulling the currency list from wikipedia and the country list from the stage.gov website. You can download the SQL here, or copy from below. Do note there are NULLs in some of the places where data was unavailable.

CREATE TABLE Countries (
  ID tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  FIPS char(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  Name varchar(35) NOT NULL,
  LongName varchar(55) NOT NULL,
  UNIQUE KEY Name (Name)

INSERT INTO Countries (FIPS, Name, LongName) VALUES
  ('AF','Afghanistan','Islamic Republic of Afghanistan'),
  ('AL','Albania','Republic of Albania'),
  ('AG','Algeria','People’s Democratic Republic of Algeria'),
  ('AN','Andorra','Principality of Andorra'),
  ('AO','Angola','Republic of Angola'),
  ('AC','Antigua and Barbuda','Antiqua and Barbuda'),
  ('AR','Argentina','Argentine Republic'),
  ('AM','Armenia','Republic of Armenia'),
  ('AS','Australia','Commonwealth of Australia'),
  ('AU','Austria','Republic of Austria'),
  ('AJ','Azerbaijan','Republic of Azerbaijan'),
  ('BF','Bahamas, The','Commonwealth of The Bahamas'),
  ('BA','Bahrain','Kingdom of Bahrain'),
  ('BG','Bangladesh','People’s Republic of Bangladesh'),
  ('BO','Belarus','Republic of Belarus'),
  ('BE','Belgium','Kingdom of Belgium'),
  ('BN','Benin','Republic of Benin'),
  ('BT','Bhutan','Kingdom of Bhutan'),
  ('BL','Bolivia','Plurinational State of Bolivia'),
  ('BK','Bosnia and Herzegovina','Bosnia and Herzegovina'),
  ('BC','Botswana','Republic of Botswana'),
  ('BR','Brazil','Federative Republic of Brazil'),
  ('BX','Brunei','Brunei Darussalam'),
  ('BU','Bulgaria','Republic of Bulgaria'),
  ('UV','Burkina Faso','Burkina Faso'),
  ('BM','Burma','Union of Burma'),
  ('BY','Burundi','Republic of Burundi'),
  ('CV','Cabo Verde','Republic of Cabo Verde'),
  ('CB','Cambodia','Kingdom of Cambodia'),
  ('CM','Cameroon','Republic of Cameroon'),
  ('CT','Central African Republic','Central African Republic'),
  ('CD','Chad','Republic of Chad'),
  ('CI','Chile','Republic of Chile'),
  ('CH','China','People’s Republic of China'),
  ('CO','Colombia','Republic of Colombia'),
  ('CN','Comoros','Union of the Comoros'),
  ('CF','Congo (Brazzaville)','Republic of the Congo'),
  ('CG','Congo (Kinshasa)','Democratic Republic of the Congo'),
  ('CS','Costa Rica','Republic of Costa Rica'),
  ('IV','Côte d’Ivoire','Republic of Côte d’Ivoire'),
  ('HR','Croatia','Republic of Croatia'),
  ('CU','Cuba','Republic of Cuba'),
  ('CY','Cyprus','Republic of Cyprus'),
  ('EZ','Czech Republic','Czech Republic'),
  ('DA','Denmark','Kingdom of Denmark'),
  ('DJ','Djibouti','Republic of Djibouti'),
  ('DO','Dominica','Commonwealth of Dominica'),
  ('DR','Dominican Republic','Dominican Republic'),
  ('EC','Ecuador','Republic of Ecuador'),
  ('EG','Egypt','Arab Republic of Egypt'),
  ('ES','El Salvador','Republic of El Salvador'),
  ('EK','Equatorial Guinea','Republic of Equatorial Guinea'),
  ('ER','Eritrea','State of Eritrea'),
  ('EN','Estonia','Republic of Estonia'),
  ('ET','Ethiopia','Federal Democratic Republic of Ethiopia'),
  ('FJ','Fiji','Republic of Fiji'),
  ('FI','Finland','Republic of Finland'),
  ('FR','France','French Republic'),
  ('GB','Gabon','Gabonese Republic'),
  ('GA','Gambia, The','Republic of The Gambia'),
  ('GM','Germany','Federal Republic of Germany'),
  ('GH','Ghana','Republic of Ghana'),
  ('GR','Greece','Hellenic Republic'),
  ('GT','Guatemala','Republic of Guatemala'),
  ('GV','Guinea','Republic of Guinea'),
  ('PU','Guinea-Bissau','Republic of Guinea-Bissau'),
  ('GY','Guyana','Co-operative Republic of Guyana'),
  ('HA','Haiti','Republic of Haiti'),
  ('VT','Holy See','Holy See'),
  ('HO','Honduras','Republic of Honduras'),
  ('IC','Iceland','Republic of Iceland'),
  ('IN','India','Republic of India'),
  ('ID','Indonesia','Republic of Indonesia'),
  ('IR','Iran','Islamic Republic of Iran'),
  ('IZ','Iraq','Republic of Iraq'),
  ('IS','Israel','State of Israel'),
  ('IT','Italy','Italian Republic'),
  ('JO','Jordan','Hashemite Kingdom of Jordan'),
  ('KZ','Kazakhstan','Republic of Kazakhstan'),
  ('KE','Kenya','Republic of Kenya'),
  ('KR','Kiribati','Republic of Kiribati'),
  ('KN','Korea, North','Democratic People’s Republic of Korea'),
  ('KS','Korea, South','Republic of Korea'),
  ('KV','Kosovo','Republic of Kosovo'),
  ('KU','Kuwait','State of Kuwait'),
  ('KG','Kyrgyzstan','Kyrgyz Republic'),
  ('LA','Laos','Lao People’s Democratic Republic'),
  ('LG','Latvia','Republic of Latvia'),
  ('LE','Lebanon','Lebanese Republic'),
  ('LT','Lesotho','Kingdom of Lesotho'),
  ('LI','Liberia','Republic of Liberia'),
  ('LS','Liechtenstein','Principality of Liechtenstein'),
  ('LH','Lithuania','Republic of Lithuania'),
  ('LU','Luxembourg','Grand Duchy of Luxembourg'),
  ('MK','Macedonia','Republic of Macedonia'),
  ('MA','Madagascar','Republic of Madagascar'),
  ('MI','Malawi','Republic of Malawi'),
  ('MV','Maldives','Republic of Maldives'),
  ('ML','Mali','Republic of Mali'),
  ('MT','Malta','Republic of Malta'),
  ('RM','Marshall Islands','Republic of the Marshall Islands'),
  ('MR','Mauritania','Islamic Republic of Mauritania'),
  ('MP','Mauritius','Republic of Mauritius'),
  ('MX','Mexico','United Mexican States'),
  ('FM','Micronesia, Federated States of','Federated States of Micronesia'),
  ('MD','Moldova','Republic of Moldova'),
  ('MN','Monaco','Principality of Monaco'),
  ('MO','Morocco','Kingdom of Morocco'),
  ('MZ','Mozambique','Republic of Mozambique'),
  ('WA','Namibia','Republic of Namibia'),
  ('NR','Nauru','Republic of Nauru'),
  ('NP','Nepal','Federal Democratic Republic of Nepal'),
  ('NL','Netherlands','Kingdom of the Netherlands'),
  ('NZ','New Zealand','New Zealand'),
  ('NU','Nicaragua','Republic of Nicaragua'),
  ('NG','Niger','Republic of Niger'),
  ('NI','Nigeria','Federal Republic of Nigeria'),
  ('NO','Norway','Kingdom of Norway'),
  ('MU','Oman','Sultanate of Oman'),
  ('PK','Pakistan','Islamic Republic of Pakistan'),
  ('PS','Palau','Republic of Palau'),
  ('PM','Panama','Republic of Panama'),
  ('PP','Papua New Guinea','Independent State of Papua New Guinea'),
  ('PA','Paraguay','Republic of Paraguay'),
  ('PE','Peru','Republic of Peru'),
  ('RP','Philippines','Republic of the Philippines'),
  ('PL','Poland','Republic of Poland'),
  ('PO','Portugal','Portuguese Republic'),
  ('QA','Qatar','State of Qatar'),
  ('RS','Russia','Russian Federation'),
  ('RW','Rwanda','Republic of Rwanda'),
  ('SC','Saint Kitts and Nevis','Federation of Saint Kitts and Nevis'),
  ('ST','Saint Lucia','Saint Lucia'),
  ('VC','Saint Vincent and the Grenadines','Saint Vincent and the Grenadines'),
  ('WS','Samoa','Independent State of Samoa'),
  ('SM','San Marino','Republic of San Marino'),
  ('TP','Sao Tome and Principe','Democratic Republic of Sao Tome and Principe'),
  ('SA','Saudi Arabia','Kingdom of Saudi Arabia'),
  ('SG','Senegal','Republic of Senegal'),
  ('RI','Serbia','Republic of Serbia'),
  ('SE','Seychelles','Republic of Seychelles'),
  ('SL','Sierra Leone','Republic of Sierra Leone'),
  ('SN','Singapore','Republic of Singapore'),
  ('LO','Slovakia','Slovak Republic'),
  ('SI','Slovenia','Republic of Slovenia'),
  ('BP','Solomon Islands','Solomon Islands'),
  ('SO','Somalia','Federal Republic of Somalia'),
  ('SF','South Africa','Republic of South Africa'),
  ('OD','South Sudan','Republic of South Sudan'),
  ('SP','Spain','Kingdom of Spain'),
  ('CE','Sri Lanka','Democratic Socialist Republic of Sri Lanka'),
  ('SU','Sudan','Republic of the Sudan'),
  ('NS','Suriname','Republic of Suriname'),
  ('WZ','Swaziland','Kingdom of Swaziland'),
  ('SW','Sweden','Kingdom of Sweden'),
  ('SZ','Switzerland','Swiss Confederation'),
  ('SY','Syria','Syrian Arab Republic'),
  ('TI','Tajikistan','Republic of Tajikistan'),
  ('TZ','Tanzania','United Republic of Tanzania'),
  ('TH','Thailand','Kingdom of Thailand'),
  ('TT','Timor-Leste','Democratic Republic of Timor-Leste'),
  ('TO','Togo','Togolese Republic'),
  ('TN','Tonga','Kingdom of Tonga'),
  ('TD','Trinidad and Tobago','Republic of Trinidad and Tobago'),
  ('TS','Tunisia','Republic of Tunisia'),
  ('TU','Turkey','Republic of Turkey'),
  ('UG','Uganda','Republic of Uganda'),
  ('AE','United Arab Emirates','United Arab Emirates'),
  ('UK','United Kingdom','United Kingdom of Great Britain and Northern Ireland'),
  ('US','United States','United States of America'),
  ('UY','Uruguay','Oriental Republic of Uruguay'),
  ('UZ','Uzbekistan','Republic of Uzbekistan'),
  ('NH','Vanuatu','Republic of Vanuatu'),
  ('VE','Venezuela','Bolivarian Republic of Venezuela'),
  ('VM','Vietnam','Socialist Republic of Vietnam'),
  ('YM','Yemen','Republic of Yemen'),
  ('ZA','Zambia','Republic of Zambia'),
  ('ZI','Zimbabwe','Republic of Zimbabwe');

CREATE TABLE Currencies (
  ID tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  ISOCode char(3) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  NumberToBasic smallint(5) unsigned DEFAULT NULL,
  Symbol varchar(6) DEFAULT NULL,
  Name varchar(55) DEFAULT NULL,
  UNIQUE KEY Name (Name)

INSERT INTO Currencies (ISOCode, NumberToBasic, Symbol, Name) VALUES
  ('AFN',100,'؋','Afghan afghani'),
  ('ALL',100,'L','Albanian lek'),
  (NULL,100,'£','Alderney pound'),
  ('DZD',100,'د.ج','Algerian dinar'),
  ('AOA',100,'Kz','Angolan kwanza'),
  ('ARS',100,'$','Argentine peso'),
  ('AMD',100,'֏','Armenian dram'),
  ('AWG',100,'ƒ','Aruban florin'),
  (NULL,100,'£','Ascension pound'),
  ('AUD',100,'$','Australian dollar'),
  ('AZN',100,'₼','Azerbaijani manat'),
  ('BSD',100,'$','Bahamian dollar'),
  ('BHD',1000,'.د.ب','Bahraini dinar'),
  ('BDT',100,'৳','Bangladeshi taka'),
  ('BBD',100,'$','Barbadian dollar'),
  ('BYR',100,'Br','Belarusian ruble'),
  ('BZD',100,'$','Belize dollar'),
  ('BMD',100,'$','Bermudian dollar'),
  ('BTN',100,'Nu.','Bhutanese ngultrum'),
  ('BOB',100,'Bs.','Bolivian boliviano'),
  ('BAM',100,'KM','Bosnia and Herzegovina convertible mark'),
  ('BWP',100,'P','Botswana pula'),
  ('BRL',100,'R$','Brazilian real'),
  ('GBP',100,'£','British pound'),
  (NULL,100,'$','British Virgin Islands dollar'),
  ('BND',100,'$','Brunei dollar'),
  ('BGN',100,'лв','Bulgarian lev'),
  ('MMK',100,'Ks','Burmese kyat'),
  ('BIF',100,'Fr','Burundian franc'),
  ('KHR',100,'៛','Cambodian riel'),
  ('CAD',100,'$','Canadian dollar'),
  ('CVE',100,'$','Cape Verdean escudo'),
  ('KYD',100,'$','Cayman Islands dollar'),
  ('XAF',100,'Fr','Central African CFA franc'),
  ('XPF',100,'Fr','CFP franc'),
  ('CLP',100,'$','Chilean peso'),
  ('CNY',100,'¥','Chinese yuan'),
  ('COP',100,'$','Colombian peso'),
  ('KMF',100,'Fr','Comorian franc'),
  ('CDF',100,'Fr','Congolese franc'),
  (NULL,100,'$','Cook Islands dollar'),
  ('CRC',100,'₡','Costa Rican colón'),
  ('HRK',100,'kn','Croatian kuna'),
  ('CUC',100,'$','Cuban convertible peso'),
  ('CUP',100,'$','Cuban peso'),
  ('CZK',100,'Kč','Czech koruna'),
  ('DKK',100,'kr','Danish krone'),
  ('DJF',100,'Fr','Djiboutian franc'),
  ('DOP',100,'$','Dominican peso'),
  ('XCD',100,'$','East Caribbean dollar'),
  ('EGP',100,'£','Egyptian pound'),
  ('ERN',100,'Nfk','Eritrean nakfa'),
  ('ETB',100,'Br','Ethiopian birr'),
  ('FKP',100,'£','Falkland Islands pound'),
  (NULL,100,'kr','Faroese króna'),
  ('FJD',100,'$','Fijian dollar'),
  ('GMD',100,'D','Gambian dalasi'),
  ('GEL',100,'ლ','Georgian lari'),
  ('GHS',100,'₵','Ghana cedi'),
  ('GIP',100,'£','Gibraltar pound'),
  ('GTQ',100,'Q','Guatemalan quetzal'),
  ('GGP',100,'£','Guernsey pound'),
  ('GNF',100,'Fr','Guinean franc'),
  ('GYD',100,'$','Guyanese dollar'),
  ('HTG',100,'G','Haitian gourde'),
  ('HNL',100,'L','Honduran lempira'),
  ('HKD',100,'$','Hong Kong dollar'),
  ('HUF',100,'Ft','Hungarian forint'),
  ('ISK',100,'kr','Icelandic króna'),
  ('INR',100,'₹','Indian rupee'),
  ('IDR',100,'Rp','Indonesian rupiah'),
  ('IRR',100,'﷼','Iranian rial'),
  ('IQD',1000,'ع.د','Iraqi dinar'),
  ('ILS',100,'₪','Israeli new shekel'),
  ('JMD',100,'$','Jamaican dollar'),
  ('JPY',100,'¥','Japanese yen'),
  ('JEP',100,'£','Jersey pound'),
  ('JOD',100,'د.ا','Jordanian dinar'),
  ('KZT',100,'₸','Kazakhstani tenge'),
  ('KES',100,'Sh','Kenyan shilling'),
  (NULL,100,'$','Kiribati dollar'),
  ('KWD',1000,'د.ك','Kuwaiti dinar'),
  ('KGS',100,'лв','Kyrgyzstani som'),
  ('LAK',100,'₭','Lao kip'),
  ('LBP',100,'ل.ل','Lebanese pound'),
  ('LSL',100,'L','Lesotho loti'),
  ('LRD',100,'$','Liberian dollar'),
  ('LYD',1000,'ل.د','Libyan dinar'),
  ('MOP',100,'P','Macanese pataca'),
  ('MKD',100,'ден','Macedonian denar'),
  ('MGA',5,'Ar','Malagasy ariary'),
  ('MWK',100,'MK','Malawian kwacha'),
  ('MYR',100,'RM','Malaysian ringgit'),
  ('MVR',100,'.ރ','Maldivian rufiyaa'),
  ('IMP',100,'£','Manx pound'),
  ('MRO',5,'UM','Mauritanian ouguiya'),
  ('MUR',100,'₨','Mauritian rupee'),
  ('MXN',100,'$','Mexican peso'),
  (NULL,100,'$','Micronesian dollar'),
  ('MDL',100,'L','Moldovan leu'),
  ('MNT',100,'₮','Mongolian tögrög'),
  ('MAD',100,'د. م.','Moroccan dirham'),
  ('MZN',100,'MT','Mozambican metical'),
  (NULL,100,'դր.','Nagorno-Karabakh dram'),
  ('NAD',100,'$','Namibian dollar'),
  (NULL,100,'$','Nauruan dollar'),
  ('NPR',100,'₨','Nepalese rupee'),
  ('ANG',100,'ƒ','Netherlands Antillean guilder'),
  ('TWD',100,'$','New Taiwan dollar'),
  ('NZD',100,'$','New Zealand dollar'),
  ('NIO',100,'C$','Nicaraguan córdoba'),
  ('NGN',100,'₦','Nigerian naira'),
  (NULL,100,'$','Niue dollar'),
  ('KPW',100,'₩','North Korean won'),
  ('NOK',100,'kr','Norwegian krone'),
  ('OMR',1000,'ر.ع.','Omani rial'),
  ('PKR',100,'₨','Pakistani rupee'),
  (NULL,100,'$','Palauan dollar'),
  ('PAB',100,'B/.','Panamanian balboa'),
  ('PGK',100,'K','Papua New Guinean kina'),
  ('PYG',100,'₲','Paraguayan guaraní'),
  ('PEN',100,'S/.','Peruvian nuevo sol'),
  ('PHP',100,'₱','Philippine peso'),
  (NULL,100,'$','Pitcairn Islands dollar'),
  ('PLN',100,'zł','Polish złoty'),
  ('QAR',100,'ر.ق','Qatari riyal'),
  ('RON',100,'lei','Romanian leu'),
  ('RUB',100,'₽','Russian ruble'),
  ('RWF',100,'Fr','Rwandan franc'),
  (NULL,100,'Ptas','Sahrawi peseta'),
  ('SHP',100,'£','Saint Helena pound'),
  ('WST',100,'T','Samoan tālā'),
  ('STD',100,'Db','São Tomé and Príncipe dobra'),
  ('SAR',100,'ر.س','Saudi riyal'),
  ('RSD',100,'дин.','Serbian dinar'),
  ('SCR',100,'₨','Seychellois rupee'),
  ('SLL',100,'Le','Sierra Leonean leone'),
  ('SGD',100,'$','Singapore dollar'),
  ('SBD',100,'$','Solomon Islands dollar'),
  ('SOS',100,'Sh','Somali shilling'),
  (NULL,100,'Sh','Somaliland shilling'),
  ('ZAR',100,'R','South African rand'),
  (NULL,100,'£','South Georgia and the South Sandwich Islands pound'),
  ('KRW',100,'₩','South Korean won'),
  ('SSP',100,'£','South Sudanese pound'),
  ('LKR',100,'රු','Sri Lankan rupee'),
  ('SDG',100,'ج.س.','Sudanese pound'),
  ('SRD',100,'$','Surinamese dollar'),
  ('SZL',100,'L','Swazi lilangeni'),
  ('SEK',100,'kr','Swedish krona'),
  ('CHF',100,'Fr','Swiss franc'),
  ('SYP',100,'£','Syrian pound'),
  ('TJS',100,'ЅМ','Tajikistani somoni'),
  ('TZS',100,'Sh','Tanzanian shilling'),
  ('THB',100,'฿','Thai baht'),
  ('TOP',100,'T$','Tongan paʻanga'),
  ('PRB',100,'р.','Transnistrian ruble'),
  ('TTD',100,'$','Trinidad and Tobago dollar'),
  (NULL,100,'£','Tristan da Cunha pound'),
  ('TND',1000,'د.ت','Tunisian dinar'),
  ('TRY',100,'₺','Turkish lira'),
  ('TMT',100,'m','Turkmenistan manat'),
  (NULL,100,'$','Tuvaluan dollar'),
  ('UGX',100,'Sh','Ugandan shilling'),
  ('UAH',100,'₴','Ukrainian hryvnia'),
  ('AED',100,'د.إ','United Arab Emirates dirham'),
  ('USD',100,'$','United States dollar'),
  ('UYU',100,'$','Uruguayan peso'),
  ('UZS',100,NULL,'Uzbekistani som'),
  ('VUV',NULL,'Vt','Vanuatu vatu'),
  ('VEF',100,'Bs F','Venezuelan bolívar'),
  ('VND',10,'₫','Vietnamese đồng'),
  ('XOF',100,'Fr','West African CFA franc'),
  ('YER',100,'﷼','Yemeni rial'),
  ('ZMW',100,'ZK','Zambian kwacha');
Weird filename encoding issues on windows

So somehow all of the file names in my Rammstein music directory, and some in my Daft Punk, had characters with diacritics replaced with an invalid character. I pasted one of such filenames into a hex editor to evaluate what the problem was. First, I should note that Windows encodes its filenames (and pretty much everything) in UTF16. Everything else in the world (mostly) has settled on UTF8, which is a much better encoding for many reasons. So during some file copy/conversion at some point in the directories’ lifetime, the file names had done a freakish (utf16*)(utf16->utf8) rename, or something to that extent. I had noticed that all I needed to do was to replace the first 2 bytes of the diacritic character with a different byte. Namely “EF 8x” to “Cx”, and the rest of the bytes for the character were fine. So if anyone ever needs it, here is the bash script.

for i in `find -type f | grep -P '\xEF[\x80-\x8F]'`; do
	TO=$(echo "$i" | perl -pi -e 's/\xEF([\x80-\x8F])/pack("C", ord($1)+(0xC0-0x80))/e');
	echo Renaming "'$FROM'" to "'$TO'"
	mv "$FROM" "$TO"

I may need to expand the range beyond the x80-x8F range, but am unsure at this point. I only confirmed the range x82-x83.

XCode Compiler Fail
Now this is just ridiculous

While I’ve been encountering more bugs than I can count on both hands while working with XCode, this one takes the cake. Clang (the compiler) was throwing the following errors while it was trying to compile one of its objective C source files (.m extension).

clang: error: unable to execute command: Segmentation fault: 11
clang: error: clang frontend command failed due to signal (use -v to see invocation)
Apple LLVM version 7.0.2 (clang-700.1.81)
Target: arm-apple-darwin14.5.0
Thread model: posix
clang: note: diagnostic msg: PLEASE submit a bug report to http://developer.apple.com/bugreporter/ and include the crash backtrace, preprocessed source, and associated run script.
clang: error: unable to execute command: Segmentation fault: 11
clang: note: diagnostic msg: Error generating preprocessed source(s).

The fix... was to keep the specific source file open in an XCode window ~.~ . How the heck do you integrate the [CLI] compiler so much into the IDE that this could happen? Or is this simply a weird file system thing? I should note that my XCode project directory, with all files, is located on a VMware volume share.

Backing up just the user settings in cPanel

One of the companies I work for recently moved one of our cPanel servers to a new collocation, still running cPanel. We decided to use a new backup solution called r1soft, which so far has been working spectacularly. I’d love to use it for my personal computers, except the licenses, which are geared towards enterprise business, are way too costly.

However, since r1soft only backs up files (on the incrementally block level, yay) you can’t use it to restore a cPanel account. It can only restore things like the user’s home directory and SQL databases. Because of this, when we had need to restore an entire account today, and found out there is no easy/quick way to do it, we were up a creek. The obvious future solution for this would be to use cPanel’s backup (or legacy backup) systems, but unfortunately, you can’t easily set them to not backup the user’s databases and home directory, which can be very large, and are already taken care of by r1soft. I ended up adding the following script, ran nightly via cron, to back up user account settings.

It saves all the user settings under the backup path in their own directory, uncompressed, and named cpmove-USERNAME. It is best to do it this way so r1soft’s incremental backups don’t have much extra work if anything changes. Make sure to change line 3 in the following script to the path where you want backups to occur.

#Create and move to backup directory
mkdir -p $BACKUPDIR #Make sure the directory exists

#Remove old backups
rm -rf cpmove-*

#Loop over accounts
for USER in `/usr/sbin/whmapi1 listaccts | grep -oP '(?<=user: )\w+$' | sort -u`; do
  #Backup the account
  /scripts/pkgacct --nocompress --skipbwdata --skiphomedir --skiplogs --skipmysql --skipmailman $USER ./

  #Extract from and remove the tar container file
  tar -xvf cpmove-$USER.tar
  rm -f cpmove-$USER.tar

  #Save MySQL user settings
  mysqldump --compact -fnt -w "User LIKE '$USER""_%'" mysql user db tables_priv columns_priv procs_priv proxies_priv \
  | perl -pe "s~('|NULL)\),\('~\1),\n('~ig" \
  > cpmove-$USER/mysql-users.sql

This script skips a few backup items that need to be noted. Mailman, logs, homedir, and bandwidth data should all be easy 1:1 copy over restores from r1soft. I excluded them because those can take up a lot of room, which we want r1soft to handle. The same goes for MySQL, except that your MySQL users are not backed up to your account, which is why I added the final section.

Do note, for the final section, the line starting with “| perl” is optional. It is there to separate the insert rows into their own lines. A very minor warning though; it would also pick up cases where the last field in MySQL’s user table ends in “NULL),(”. This would only happen if someone is trying to be malicious and knew about this script, and even then, it couldn’t harm anything.

Bonus note: To restore a MySQL database which does not use a shared-file (like InnoDB does by default), you could actually stop the MySQL server, copy over the binary database files, and start the server back up.